MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Vlookup or what?

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


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


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.


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,


Adjust for the sheetname and ranges to your situation.