Need help on VLOOKUP with 2 or more Criteria

**chico.corrales** · 01-29-2013, 07:59 PM

Hi,

I need help on VLOOKUP with 2 or more Criteria on another sheet.
one criteria would be date dependent (manual input of date) and the
other would be based on a constant/fixed cell with ID number.

e.g.

Sheet1!
Date - ID - Name - Area

Sheet2!
Date: (input manualy)

Date - ID - Name - Area

on the second sheet i need the formula that if i put a date say..27-Jan-13
it will lookup on Sheet1 based also on ID no. which is a constant value in
Sheet2.

Hope u guys help me on this one...Thanks a lot...

i have attached the excel sample file..just in case...:-)

**benishiryo** · 01-29-2013, 08:31 PM

hi chico.corrales. in Excel 2007 & above, you can try this in B3:
=IFERROR(INDEX(Sheet1!B$2:B$11,MATCH(TRUE,INDEX(Sheet1!$B$2:$B$11&Sheet1!$C$2:$C$11=$C$1&$C3,),0)),"")

or an array formula as such:
=IFERROR(INDEX(Sheet1!B$2:B$11,MATCH($C$1&$C3,Sheet1!$B$2:$B$11&Sheet1!$C$2:$C$11,0)),"")
for array formulas, you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER