Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tricky lookup problem
I'm having trouble looking up a value in my table. Here's a small
portion of my table and what i'd like to do: 15 20 25 e mph mph mph (%) R (ft) R (ft) R (ft) 1.5 932 1640 2370 2 676 1190 1720 2.2 605 1070 1550 2.4 546 959 1400 2.6 496 872 1280 2.8 453 796 1170 3 415 730 1070 3.2 382 672 985 What i'm trying to do is lookup a speed across the top, then depending on what speed, go down until i find my given radius, then depending on what the radius is, slide across to the left to find out my final e%. For example if my speed was 20 and my radius was 1070, my e% would be 2.2. But now how do i use the lookup when my speed and radius might be different? Any help would be greatly appreciated!!! I've tried MATCH() and HLOOKUP () combo, but don't always know what columns to look down. Thanks, DK |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tricky lookup problem
I put your example table, including the 3 header rows, in cells
A1:D11. I used B20 to enter the speed (with the label "Speed" in A20), and B21 to enter the radius (with "Radius" in A21 and "e" in A23), and then put this formula in B23: =INDEX(A4:A11,MATCH(B21,INDIRECT(CHAR(65+MATCH(B20 ,B1:D1))&"4:"&CHAR (65+MATCH(B20,B1:D1))&"11"),-1)) This will cope with data out to column AA (instead of D1 in the range), and if you have data below row 11 then change the two 11s to suit. Hope this helps. Pete On Nov 27, 3:08*pm, dksaluki wrote: I'm having trouble looking up a value in my table. Here's a small portion of my table and what i'd like to do: * * * * * *15 * * * 20 * * * *25 e * * * * mph * * mph * * mph (%) * * *R (ft) * *R (ft) * *R (ft) 1.5 * * *932 * * 1640 * * 2370 2 * * * * 676 * * 1190 * *1720 2.2 * * *605 * * 1070 * * 1550 2.4 * * *546 * * *959 * * *1400 2.6 * * *496 * * *872 * * 1280 2.8 * * *453 * * *796 * * 1170 3 * * * * 415 * * *730 * * *1070 3.2 * * *382 * * *672 * * * 985 What i'm trying to do is lookup a speed across the top, then depending on what speed, go down until i find my given radius, then depending on what the radius is, slide across to the left to find out my final e%. For example if my speed was 20 and my radius was 1070, my e% would be 2.2. But now how do i use the lookup when my speed and radius might be different? Any help would be greatly appreciated!!! I've tried MATCH() and HLOOKUP () combo, but don't always know what columns to look down. Thanks, DK |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tricky lookup problem
DK,
First of all, i suggest you move column "e%" to the end (see below) A B C D 1 mph mph mph e 2 R (ft) R (ft) R (ft) (%) 3 15 20 25 4 932 1640 2370 1.5 5 676 1190 1720 2.0 6 605 1070 1550 2.2 7 546 959 1400 2.4 8 496 872 1280 2.6 9 453 796 1170 2.8 10 415 730 1070 3.0 11 382 672 985 3.2 Create 2 auxiliares cells. For example: Speed in cell H4 and radius in cell H5. In other cell put: =IF(H4=15;VLOOKUP(H5;$B$5:$E$12;4;0);IF(H4=20;VLOO KUP($C$5:$E $12;3;0);IF(H4=25;VLOOKUP(H5;$D$5:$E$12;2;0);""))) Best Regards, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tricky lookup problem
Depends on the requirements as far as exact matches or approximate matches.
B14 = speed B15 = R =INDEX(A4:A11,MATCH(B15,INDEX(B4:D11,,MATCH(B14,B1 :D1)),-1)) -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... I put your example table, including the 3 header rows, in cells A1:D11. I used B20 to enter the speed (with the label "Speed" in A20), and B21 to enter the radius (with "Radius" in A21 and "e" in A23), and then put this formula in B23: =INDEX(A4:A11,MATCH(B21,INDIRECT(CHAR(65+MATCH(B20 ,B1:D1))&"4:"&CHAR (65+MATCH(B20,B1:D1))&"11"),-1)) This will cope with data out to column AA (instead of D1 in the range), and if you have data below row 11 then change the two 11s to suit. Hope this helps. Pete On Nov 27, 3:08 pm, dksaluki wrote: I'm having trouble looking up a value in my table. Here's a small portion of my table and what i'd like to do: 15 20 25 e mph mph mph (%) R (ft) R (ft) R (ft) 1.5 932 1640 2370 2 676 1190 1720 2.2 605 1070 1550 2.4 546 959 1400 2.6 496 872 1280 2.8 453 796 1170 3 415 730 1070 3.2 382 672 985 What i'm trying to do is lookup a speed across the top, then depending on what speed, go down until i find my given radius, then depending on what the radius is, slide across to the left to find out my final e%. For example if my speed was 20 and my radius was 1070, my e% would be 2.2. But now how do i use the lookup when my speed and radius might be different? Any help would be greatly appreciated!!! I've tried MATCH() and HLOOKUP () combo, but don't always know what columns to look down. Thanks, DK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tricky sum problem | Excel Discussion (Misc queries) | |||
Tricky array problem…. | Excel Discussion (Misc queries) | |||
Calculating time between values (tricky Problem) | Excel Discussion (Misc queries) | |||
Calculating time between values (tricky Problem) | Excel Worksheet Functions | |||
Need help with a tricky problem of placing a number within an array... | Excel Worksheet Functions |