Lookup table for two conditions

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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),MATCH(H3,A1:E1)

HTH

 

[Guest]

Thank you both, your suggestions worked great.