# Test # against range of #'s in chart, if in range return a #

#### Gregg

##### New Member
I would like to test a number to see if it falls between Column A & B on a certain row. If it does return the number in Column C. If not drop down a row and repeat the test. The number will be somewhere inbetween A1 and B10. It is not likely to equal the number at the high or low end of any range. Sheet 1 contains the number that is know and the table to compare the range against is on sheet 2.

I can do this in Lotus using the IF <=> and the {branch \} commands. Don't know how to use the {Branch \} in Excel.

Assume Sheet 1:A1 = 23.5 ; Then the macro or formula should return 2 according to the table below.

A B C
row 1 10 20 1
row 2 21 30 2
row 3 31 40 3

Thank you.

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Re: Test # against range of #'s in chart, if in range return

Try this:
Book2
ABCD
110201
221302
33140332
43
Sheet1

A1 in the formula refers to the top-leftmost cell in the range.

Re: Test # against range of #'s in chart, if in range return

I think your just looking for a VLookup, like so
Book1
ABCDE
1LowerUpperReturnNUMBERLookup
21020123.52
321302
431403
Sheet4

Re: Test # against range of #'s in chart, if in range return

=VLOOKUP(A1,Table,3)

or with some control:

=IF(A1>=MIN(INDEX(Table,0,1)),VLOOKUP(A1,Table,3),"")

Or even:

=MATCH(A1,INDEX(Table,0,1))

given the figures in the 3rd column of your table.

Table refers to the area housing the table.

Re: Test # against range of #'s in chart, if in range return

Thank you for all your responses. Phantom1975 formula works.
You have saved me a lot of hair...I have been pulling it out.

Have a great day,
Gregg

Replies
0
Views
190
Replies
2
Views
207
Replies
1
Views
101
Replies
7
Views
154
Replies
5
Views
271

1,202,898
Messages
6,052,437
Members
444,581
Latest member
naninamu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back