Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup table between two ranges
Hi,
I would like to lookup a value (entered in cell D1) in the table below. For example if it were between 0 and 2 it would return R1, if it were between 3 and 5 it would return R2, etc. (starting in column A1) 0 2 R1 3 5 R2 6 8 R3 Any help would be gratefully received |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup table between two ranges
"LROCCO" wrote in message
... Hi, I would like to lookup a value (entered in cell D1) in the table below. For example if it were between 0 and 2 it would return R1, if it were between 3 and 5 it would return R2, etc. (starting in column A1) 0 2 R1 3 5 R2 6 8 R3 Any help would be gratefully received You can do this easily with a non-exact vlookup. You don't need the second column; just enter 0,3,6 in A1:A3 and R1,R2,R3 in B1:B3. Put the value you want to lookup in D1 and use this formula: =VLOOKUP(D1,A1:B3,2,1) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup table between two ranges
On 5 Dec, 13:05, "Stephen" <none wrote:
"LROCCO" wrote in message ... Hi, I would like to lookup a value (entered in cell D1) in the table below. For example if it were between 0 and 2 it would return R1, if it were between 3 and 5 it would return R2, etc. (starting in column A1) 0 2 R1 3 5 R2 6 8 R3 Any help would be gratefully received You can do this easily with a non-exact vlookup. You don't need the second column; just enter 0,3,6 in A1:A3 and R1,R2,R3 in B1:B3. Put the value you want to lookup in D1 and use this formula: =VLOOKUP(D1,A1:B3,2,1) Thanks for that, works great. I guess it would be more difficult if the numbers didn't follow on from each other, i.e. 2 4 R1 7 10 R2 12 17 R3 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup table between two ranges
"LROCCO" wrote in message
... On 5 Dec, 13:05, "Stephen" <none wrote: "LROCCO" wrote in message ... Hi, I would like to lookup a value (entered in cell D1) in the table below. For example if it were between 0 and 2 it would return R1, if it were between 3 and 5 it would return R2, etc. (starting in column A1) 0 2 R1 3 5 R2 6 8 R3 Any help would be gratefully received You can do this easily with a non-exact vlookup. You don't need the second column; just enter 0,3,6 in A1:A3 and R1,R2,R3 in B1:B3. Put the value you want to lookup in D1 and use this formula: =VLOOKUP(D1,A1:B3,2,1) Thanks for that, works great. I guess it would be more difficult if the numbers didn't follow on from each other, i.e. 2 4 R1 7 10 R2 12 17 R3 Well, what would you want to be returned for 5, etc.? You could make the lookup table like this, for example: 2 R1 5 "invalid" 7 R2 11 "invalid" 12 R3 18 "invalid" |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup table between two ranges
On 5 Dec, 14:09, "Stephen" <none wrote:
"LROCCO" wrote in message ... On 5 Dec, 13:05, "Stephen" <none wrote: "LROCCO" wrote in message ... Hi, I would like to lookup a value (entered in cell D1) in the table below. For example if it were between 0 and 2 it would return R1, if it were between 3 and 5 it would return R2, etc. (starting in column A1) 0 2 R1 3 5 R2 6 8 R3 Any help would be gratefully received You can do this easily with a non-exact vlookup. You don't need the second column; just enter 0,3,6 in A1:A3 and R1,R2,R3 in B1:B3. Put the value you want to lookup in D1 and use this formula: =VLOOKUP(D1,A1:B3,2,1) Thanks for that, works great. I guess it would be more difficult if the numbers didn't follow on from each other, i.e. 2 4 R1 7 10 R2 12 17 R3 Well, what would you want to be returned for 5, etc.? You could make the lookup table like this, for example: 2 R1 5 "invalid" 7 R2 11 "invalid" 12 R3 18 "invalid"- Hide quoted text - - Show quoted text - Yes of course, makes sense, nice one |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Name ranges and Lookup | Excel Worksheet Functions | |||
Lookup Value between 2 ranges | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
vlookup using two table ranges | Excel Worksheet Functions | |||
Ranges with in a LOOKUP | Excel Worksheet Functions |