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

[Gregg]

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.

 

[phantom1975]

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

Try this:

Book2
	A	B	C	D
1	10	20	1
2	21	30	2
3	31	40	3	32
4				3
Sheet1

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

 

[GorD]

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

I think your just looking for a VLookup, like so

Book1
	A	B	C	D	E
1	Lower	Upper	Return	NUMBER	Lookup
2	10	20	1	23.5	2
3	21	30	2
4	31	40	3
Sheet4

 

[Aladin Akyurek]

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.

 

[Gregg]

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