Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup table for two conditions
I want to set up a lookup command based on two criteria. I'm not sure which
step to take. The table has a column range of A:M and a row range of 6:711... My Table is set up as so: SAZ Housing 2006/07 2007/08 2008/09 1110823 SFD 100 250 500 1110823 MFA 25 50 90 1130401 SFD 0 0 60 1130401 MFA 50 75 0 1130402 SFD 5 10 15 1130402 MFA 25 20 15 What I am trying to do is set up a lookup command to lookup the SAZ and Housing (i.e. looking up 1110823 and SFD would return 100 for year 2006/07, or 1130402 and MFA would return 15 for 2008/09, etc...). What steps should I take? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup table for two conditions
You may use an array formula like this:
=INDEX($C$2:$C$7,MATCH(1,--($A$2:$A$7=1110823)*--($B$2:$B$7="SFD"),0)) Enter it with Crtl+Shift+Enter. You may need to change the ranges to fit your specific case, and you may want to change the hardcoded comparisons by cell references. This formula returns the value from the C column (2006/07), if you want other column change $C$2:$C$7 to whatever you need. Hope this helps, Miguel. "Giantrobot" wrote: I want to set up a lookup command based on two criteria. I'm not sure which step to take. The table has a column range of A:M and a row range of 6:711... My Table is set up as so: SAZ Housing 2006/07 2007/08 2008/09 1110823 SFD 100 250 500 1110823 MFA 25 50 90 1130401 SFD 0 0 60 1130401 MFA 50 75 0 1130402 SFD 5 10 15 1130402 MFA 25 20 15 What I am trying to do is set up a lookup command to lookup the SAZ and Housing (i.e. looking up 1110823 and SFD would return 100 for year 2006/07, or 1130402 and MFA would return 15 for 2008/09, etc...). What steps should I take? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup table for two conditions
TRY:
Assuming data in colums A to E =INDEX(A3:E8,MATCH(1,(A3:A8=1110823)*(B3:B8="SFD") ,0),MATCH("2006/7",A1:E1) Enter as array formula i.e. with Ctrl+SHift+Enter. Excel will put curly brackets {} round the formula. You can replace the constants with cells: =INDEX(A3:E8,MATCH(1,(A3:A8=H1)*(B3:B8=H2),0),MATC H(H3,A1:E1) HTH "Giantrobot" wrote: I want to set up a lookup command based on two criteria. I'm not sure which step to take. The table has a column range of A:M and a row range of 6:711... My Table is set up as so: SAZ Housing 2006/07 2007/08 2008/09 1110823 SFD 100 250 500 1110823 MFA 25 50 90 1130401 SFD 0 0 60 1130401 MFA 50 75 0 1130402 SFD 5 10 15 1130402 MFA 25 20 15 What I am trying to do is set up a lookup command to lookup the SAZ and Housing (i.e. looking up 1110823 and SFD would return 100 for year 2006/07, or 1130402 and MFA would return 15 for 2008/09, etc...). What steps should I take? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup table for two conditions
Thank you both, your suggestions worked great.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum cells in table using column and row conditions | Excel Worksheet Functions | |||
look up table values with multiple conditions | Excel Discussion (Misc queries) | |||
lookup? list data into summary table | Excel Worksheet Functions | |||
pivot table sort entries that don't yet appear in table | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |