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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >