Lookup a range of numbers

[Guest]

Hi,

I have a table as follows:
(Speed)
(Dist) 64k 128k 256k
5 1000 1200 1500
10 1600 1700 1800
15 2000 2100 2200

I'd like to have a formula whereby if I key in a Distance of 4 and a speed
of 256k, the active cell will show "1500".

I tried to use sumproduct but seems like sumproduct can only be used if the
Dist is exactly "5", "10", etc. The only alternative I can think of is to
list out ALL the integers for Distance. But, is there an easier way?

Thanks,
val

 

[PC]

One way

With your table starting in A1

=OFFSET(A1,MATCH(5,A1:A3,0)-1,MATCH("256k",A11,0)-1)

You can also simplify this a little by allowing the user to input the
figures without the "k" by using a custom format that puts a "k" at the end
of the number (I'm pretty sure it would be #k) Just make sure you set up
the row headers the same way so the inputs (256 in both cases) are
identical.

HTH

PC

 

[Guest]

Hi PC,

it doesn't seem to work....

There are actually supposed to be 2 cells that the user can choose - Dist
(ie, F1) & Speed (G1) and they can be different combinations.

In your formula below, seems like the "lookups" are fixed at 5 & 256k. Even
if I change the "5" to cell "F1" & "256k" to cell "G1" and the match type is
"1", if user chooses a Distance of 4 and a speed of 256 (I can drop the "k"),
the result is "N/A".

Regards,
val

 

[Guest]

Hi,

just realised I can actually combine your formula with the formula =Ceiling.

Thanks!
Val

 

[PC]

Val,

Its best to not use row 1 for the data entry fields (the formula is
searching the entire row) unless you change the "1:1" to "A11"

The numbers in column A and row 1 need to be entered & formatted as numbers
(not text), as that is how the user would input the data.

Lastly, the cell references shouldn't be encased in "" s

Try it again. It should work.

PC