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

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