MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Vlookup or what?


Posted by Orlando on October 23, 2001 4:47 AM

Hi:

I need to get a value from a range that matches two criterias. Example

In the 1st worksheet Cell A2 has 1234, cell B1 has 1212, I need a formula that will go to a range in a 2nd worksheet, where cell A2 has 1234 cell b2 has 1212 and cell c2 has $20.00 match the two critierias and return $20.00. This formula would be in the first sheet in cell B2
Thanks for your help

Orlando


Posted by Tim on October 23, 2001 5:09 AM

Please tell us what you mean more specific so we can understand you better and help you out.

Tim

Posted by Aladin Akyurek on October 23, 2001 5:28 AM

Orlando --

In B2 of Sheet1 enter: =INDEX(Sheet2!C2:C40,SUMPRODUCT(MATCH(A2&B1,Sheet2!A2:A40&Sheet2!B2:B40,0)))

Or, if you don't want an error value to show up,

=IF(MATCH(A2,Sheet2!A2:A40,0)=MATCH(B1,Sheet2!B2:B40,0),INDEX(Sheet2!C2:C40,SUMPRODUCT(MATCH(A2&B1,Sheet2!A2:A40&Sheet2!B2:B40,0))),"")

Adjust for the sheetname and ranges to your situation.

Aladin

=======