Lookup a range of numbers
 

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

One way

With your table starting in A1

=OFFSET(A1,MATCH(5,A1:A3,0)-1,MATCH("256k",A1:D1,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

"Dolphinv4" wrote in message
...
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

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

"PC" wrote:

One way

With your table starting in A1

=OFFSET(A1,MATCH(5,A1:A3,0)-1,MATCH("256k",A1:D1,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

"Dolphinv4" wrote in message
...
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

Hi,

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

Thanks!
Val

"PC" wrote:

One way

With your table starting in A1

=OFFSET(A1,MATCH(5,A1:A3,0)-1,MATCH("256k",A1:D1,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

"Dolphinv4" wrote in message
...
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

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 "A1:D1"

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

"Dolphinv4" wrote in message
...
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

"PC" wrote:

One way

With your table starting in A1

=OFFSET(A1,MATCH(5,A1:A3,0)-1,MATCH("256k",A1:D1,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

"Dolphinv4" wrote in message
...
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