1

I have tried using VLOOKUP, INDEX, and IF statements but none have quite worked.

What I want to do is match serial numbers and return a stock number, but there is data between the columns that I need to keep. Also there are only 60 serials that I am looking to link with stock numbers and 1200 serials with stocks to search through. So like this:

   A             B       C           D           E        F      
1 description  serial  location   Stock #     Stock #   Serial
2   info        aaa      1                      E1        zzz
3   info        bbb      1                      E2        sss
4   info        ccc      2                      E3        aaa

I am trying to put the formula in D, search and match column B with F and return column E to D.

pnuts
  • 58,317
  • 11
  • 87
  • 139
tractor guy
  • 13
  • 2

1 Answers1

2

Not exactly sure what is going on with the different quantities, but in D2 and copied down to suit: 

=IFERROR(INDEX(E:E,MATCH(B:B,F:F,0)),"")

seems worth a try. VLOOKUP is not a good choice here, in part because the related field is to the left of the search term.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    That does it, @pnuts!! - I have an idea... SO many people have this issue, we should open up and Excel consultancy practice that only makes lookup formulas for our clients... We'll call it IndexMatchSolvesAll and retire in the Bahamas... You in?? - I have a Pina Colada with your name in it!! :p – John Bustos Sep 11 '14 at 15:49
  • There are a few blanks where there should be a serial number but I deleted NA so that it wouldn't return an error code. When copying down the formula each sell is blank. When I replace " " with 0. Its all zeros – tractor guy Sep 11 '14 at 17:11
  • When I entered in the serials if a machine didn't have one, I put in NA but later just left the cell blank. – tractor guy Sep 11 '14 at 17:18
  • Ah HA! now it works found another N/A for a serial and a merged cell. Probably cause I brought it over from a HTML. Thank you! – tractor guy Sep 11 '14 at 17:23