excelif-statementindexingvlookupexcel-match

Matching two columns and returning a third with data in between

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.

Solution

  • 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.