MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Double Lookup


Posted by Dominick on August 31, 2000 11:59 AM

What is the easiest way to do a lookup based on 2 different criteria? Is there a way to do it without using dget? I want to be able to write a formula with the criteria in it, not have the criteria written in a row of cells.

Thanks.


Posted by Dominick on September 01, 0100 9:04 AM

Does this only work if you have text values? What if I want to look up 1 string and 1 number?

Thanks again.

Posted by Celia on September 01, 0100 4:43 PM

Diminick
Works whether values are text, numbers or both.
What error message are you getting? If it is not working, post an actual copy of the formula you are using.
Celia

Posted by Celia on August 31, 0100 5:02 PM

Dominick

Assuming:-
First look-up range is A1:A10
Second look-up range is B1:B10
Value to be returned is in C1:C10
First look-up value is in D1
Second look-up value is in E1

Array formula(Ctrl+Shift+Enter):-
=INDEX(C1:C10,MATCH(D1&E1,A1:A10&B1:B10,0))

You can substitute D1 and E1 in the formula with actual values if you wish.

Celia