Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to update column info based on column info from another worksh | Excel Discussion (Misc queries) | |||
Lookup info in one Column and then returning info in other columns | Excel Worksheet Functions | |||
Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
How do I sort by info in one column and it stay in line with info | Excel Worksheet Functions | |||
Link info in one cell to info in several cells in another column (like a database) | Excel Discussion (Misc queries) |