getting info from array but not using column / row numbers
 

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.

getting info from array but not using column / row numbers
 

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

--
Regards
Roger Govier

MS_user wrote:
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.

getting info from array but not using column / row numbers
 

Take a look at the pictu
http://img691.imageshack.us/img691/6378/nonameek.png
Micky


"MS_user" wrote:

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.

getting info from array but not using column / row numbers
 

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)

getting info from array but not using column / row numbers
 

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


"MS_user" wrote:

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.