Lookup based on multiple criteria
 

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

Lookup based on multiple criteria
 

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.

--
Biff
Microsoft Excel MVP


"John Calder" wrote in message
...
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

Lookup based on multiple criteria
 

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
---
"John Calder" wrote:
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

Lookup based on multiple criteria
 

Thanks for the qucik response

"John Calder" wrote:

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

Lookup based on multiple criteria
 

Thanks for the quck response

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"John Calder" wrote in message
...
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

Lookup based on multiple criteria
 

You're welcome!

--
Biff
Microsoft Excel MVP


"John Calder" wrote in message
...
Thanks for the quck response

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"John Calder" wrote in message
...
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