Lookup based on multiple criteria

  • Thread starter Thread starter John Calder
  • Start date Start date
J

John Calder

Hi

I run Excel 2K

I would like to have a situation where in one cell (lets say S4 I have the
letter "A") and in another cell (lets say T4, I have the number 1) and then
in cell S5 I have a lookup that is dependant on what I put in cells S4 & T4.

Cell S4 will only have the letters A,B,C or D as possible entries and cell
T4 will only have either the number 1 or 2 in it.

So, the lookup should be able to retreive any of the combinations that are
entered into cels S4 or T4

Based on the data table I have the criteria will be: any of the following

A & 1 or A & 2
B & 1 or B & 2
C & 1 or C & 2
D & 1 or D & 2

Is this possible?


Thanks


John
 
Try this array formula** :

=INDEX(C1:C10,MATCH(1,(A1:A10=S4)*(B1:B10=T4),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
You could also just normal ENTER this rendition of Biff's array expression:
=INDEX(C1:C10,MATCH(1,INDEX((A1:A10=S4)*(B1:B10=T4),),0))
It should work just as well
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
 
Thanks for the quck response

T. Valko said:
Try this array formula** :

=INDEX(C1:C10,MATCH(1,(A1:A10=S4)*(B1:B10=T4),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
Click to expand...
 
Back
Top