Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup based on 2 criteria | Excel Worksheet Functions | |||
Index/Match - Lookup based on multiple column criteria | Excel Worksheet Functions | |||
Lookup based on two criteria. . . | Excel Discussion (Misc queries) | |||
Lookup based on two criteria in 1 row | Excel Discussion (Misc queries) | |||
LOOKUP value based on 2 criteria | Excel Worksheet Functions |