Lookup table for two conditions

G

Giantrobot

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?
 
M

Miguel Zapico

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.
 
T

Toppers

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

dynamic lookup 3
complex lookup 8
Lookup in Tables 3
sumif with a lookup formula 6
Lookup assistance needed 9
Lookup and average data in an unsorted list 1
Lookup based on reference 5
Lookup table for rates? 4

Top