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
|