Need help with formula looking for dual lookups

  • Thread starter Thread starter demottae
  • Start date Start date
D

demottae

Table 1
6-10 11-15 16+
LH 750 1125 1500
OH1 1000 1500 2000
OH2 1500 2250 3000
EH1 2500 3750 5000


What i need it to do is for cell(x) to look at cell (1) for a code
(ie...LH,OH1,OH2,EH1) Then look at Cell (Y) for a number
(ie...6-10....11-15.....16+) Then i need the number in that axi
entered into cell (x)

Example from table 1..

Cell (1) has OH1.....Cell (Y) has the number 14

Cell (x) will have 1500 inserted into it

thanks for the hel
 
Hi
before entering the formula change the row headings to the lower
boundary.
6-10 -> 6
11-15 -> 11
....

Now use the following formula
=INDEX(A1:D20,MATCH("OH1",A1:A20,0),MATCH(14,A1:D1,1))

and of course you can replace the hard-coded conditions with a cell
reference. e.g.
=INDEX(A1:D20,MATCH(X1,A1:A20,0),MATCH(Y1,A1:D1,1))
 
Hi
I sent you your file with corrections. I corrected the following
errors:
- your column headings were stored as 'Text' and not as numbers. Also a
value like '16+' is not allowed. Use '16' instead
- Wrong cell ranges within MATCH
 
Back
Top