Tricky lookup problem

[dksaluki]

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

 

[Pete_UK]

I put your example table, including the 3 header rows, in cells
A111. 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,B11))&"4:"&CHAR
(65+MATCH(B20,B11))&"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

 

[Ric0999]

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;VLOOKUP($C$5:$E
$12;3;0);IF(H4=25;VLOOKUP(H5;$D$5:$E$12;2;0);"")))

Best Regards,

 

[Don Guillett]

One way if your starting column is col E.
It matches 20 in row 1
then coverts the number to a column letter
then matches 1070 in that column to find the row
then indexes col E to the row
=INDEX(E:E,MATCH(1070,INDIRECT(CHAR(MATCH(20,1:1)+64)&":"&CHAR(MATCH(20,1:1)+64)),0))

 

[T. Valko]

Depends on the requirements as far as exact matches or approximate matches.

B14 = speed
B15 = R

=INDEX(A4:A11,MATCH(B15,INDEX(B411,,MATCH(B14,B11)),-1))

--
Biff
Microsoft Excel MVP


I put your example table, including the 3 header rows, in cells
A111. 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,B11))&"4:"&CHAR
(65+MATCH(B20,B11))&"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