Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel range lookup formula
Hi there.......
I need some help with an Excel function, I did it several years ago but now I've forgotten how its done. Support I have a range of values in SheetA Name Size UserA 1.0 UserB 3.5 UserC 6.0 UserD 9.1 UserF 10 Then I have a lookup table in SheetB, what I want to do is lookup the "Size" value in SheetA and return a given value Size Setting 0.5 A 1.0 B 3.0 C 5.0 D 7.0 E 10 F so what I want to do is for each row in SheetA lookup the table in SheetB and see which size range the row falls into and return the closest setting. Eg: Name Size Setting UserA 1.0 B UserB 3.5 D UserC 6.0 E UserD 9.1 F UserF 8.5 F I want to compare the value in SheetA and return it to fall within the ranges specified by the table in SheetB. Many thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel range lookup formula
If you flip your table upside down like so:
10.....F 7......E 5......D 3......C 1......B 0.5...A Then you can use a formula like this: =INDEX(H$1:H$6,MATCH(B1,G$1:G$6,-1)) Where the table is in the range G1:H6 and B1 is the lookup value. Note that any lookup value greater than 10 and the formula will return #N/A. Any lookup value less than 0.5 and the formula will return A. -- Biff Microsoft Excel MVP wrote in message ... Hi there....... I need some help with an Excel function, I did it several years ago but now I've forgotten how its done. Support I have a range of values in SheetA Name Size UserA 1.0 UserB 3.5 UserC 6.0 UserD 9.1 UserF 10 Then I have a lookup table in SheetB, what I want to do is lookup the "Size" value in SheetA and return a given value Size Setting 0.5 A 1.0 B 3.0 C 5.0 D 7.0 E 10 F so what I want to do is for each row in SheetA lookup the table in SheetB and see which size range the row falls into and return the closest setting. Eg: Name Size Setting UserA 1.0 B UserB 3.5 D UserC 6.0 E UserD 9.1 F UserF 8.5 F I want to compare the value in SheetA and return it to fall within the ranges specified by the table in SheetB. Many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Lookup Formula | Excel Discussion (Misc queries) | |||
Need formula to lookup a named range | Excel Discussion (Misc queries) | |||
Excel finds a value that is not in the lookup range | Excel Worksheet Functions | |||
vba 97 excel lookup formula | Excel Discussion (Misc queries) | |||
HELP! Excel Lookup formula | Excel Worksheet Functions |