Forum Discussion

bfield66's avatar
bfield66
Copper Contributor
Oct 29, 2021

Vlookup with multiple criteria

Scenario: IF(vlookup(a2,sheet1!X:X,1,false)=a2    AND(vlookup(b2,sheet1!Y:Y,false)=b2 THEN(vlookup(b2,sheet1!X:Z,3,false) IF NOT, blank   In other words, look up A & B on sheet1, look at sheet ...
excel
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 29, 2021

bfield66 

=IF(AND(NOT(ISNA(VLOOKUP(E1&F1,A2:A24&B2:B24,1,FALSE))),NOT(ISNA(VLOOKUP(E1&F1,sheet2!A2:A24&sheet2!B2:B24,1,FALSE)))),VLOOKUP(sheet1!E1&sheet1!F1,CHOOSE({1,2},sheet2!A2:A24&sheet2!B2:B24,sheet2!C2:C24),2,0),"")

Enter above formula as arrayformula with ctrl+shift+enter.

Enter lookup values in E1 and F1.

Is this what you are looking for? 

 

  • bfield66's avatar
    bfield66
    Copper Contributor
    Oct 29, 2021

    OliverScheurich 

     

    Sorry, I'm having trouble translating columns. I've attached a sample sheet.

    I'm trying to match the Est and ACCUM from one sheet to the other.

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Oct 30, 2021

      bfield66 

      =IFERROR(VLOOKUP(A2&B2,CHOOSE({1,2},Sheet1!$A$2:$A$1218&Sheet1!$B$2:$B$1218,Sheet1!$C$2:$C$1218&" "&Sheet1!$D$2:$D$1218),2,0),"")

       

      Enter above formula in sheet2 as arrayformula with ctrl+shift+enter and then copy down.

       

      Formula returns results of column C and D of sheet1 if job# and item are found in sheet1.  If there is no match, an empty cell is returned.

Resources