getting info from array but not using column / row numbers

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

Roger Govier

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

tompl

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)
 
J

JLatham

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))
 

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

Display page number in footer based upon pages in the current tab 1
Creating reference numbers from column of data 3
Pull a column header from a row of numbers 1
2-dimensional array to 2 column list 3
SUM IF 1
Function or VBA Help Needed 4
Having difficulty understanding SMALL function in formula 8
X Axis:Change Numbers on Chart ? Please Help! 2

Top