Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying all combinations of a range of numbers | Excel Worksheet Functions | |||
Using COUNTIF to find numbers within a range greater than the mean | Excel Worksheet Functions | |||
How to add one number to a range of numbers | Excel Worksheet Functions | |||
How do I add a range of numbers to sum a specific total? | Excel Worksheet Functions | |||
How do I reference and sort a range of numbers in Excel 97? | Excel Worksheet Functions |