getting info from array but not using column / row numbers

  • Thread starter Thread starter MS_user
  • Start date Start date
M

MS_user

i need to get info from table:

Dist / Age 12 13 14 15
100 1.4 2.0 3.0 4.0
200 1.3 2.1 3.1 4.2
300 1.5 2.2 3.2 4.4
400 1.6 2.3 3.4 3.4

So, if i needed to get data for a 14 year old where distance is 300, I need
to search both row and column labels to get required row and column numbers
in order to retrieve data - in this case 3.2

Any ideas? I can put absolute row and column numbers in, e.g. 3,3 but i need
to search labels first based upon contents of another cells (in this case 14
and 300).

Assistance greatfully received!!! Many thanks.
 
Hi

I put 300 in H1 and 14 in I1 and then used the formula

=INDEX(A1:E5,MATCH(H1,A1:A5,0),MATCH(I1,A1:E1,0))
 
Assume that your table range is in Cells A1 to E5.
Assume that the age is entered into Cell H2.
Assume that the distance is entered into Cell H3
Use this formula to lookup the number in the table:
=HLOOKUP(H2,A1:E5,MATCH(H3,A1:A5),FALSE)
 
Another way, using exactly the same layout that Mickey showed would be to put
this formula into G3 where he put his VLOOKUP() formula:
=INDEX(A1:E5,MATCH(G1,A1:A5),MATCH(G2,A1:E1))
 
Back
Top