when you have too many conditions to use IF

[curious1]

I want to determine between which values, in colB, cell A1 falls and report the higher end of the range from colB. So, say A1=789 and col B is the array of numbers below. I would like a function that returns 999.

I thought I could use a nested vlookup with a range lookup of TRUE but that forces the return of the lower value in the range, not the higher. Maybe MAX? Stumped.

299
399
549
749
999
1299
1499
1749
1999
2499
2999
3499

 

[Peter_SSs]

Like this?

Excel Workbook
	A	B	C	D
1	789	299		999
2		399
3		549
4		749
5		999
6		1299
7		1499
8		1749
9		1999
10		2499
11		2999
12		3499
Sheet5

 

[curious1]

*Exactly* like that! Thank you!

 

[Aladin Akyurek]

Also...

=SMALL(B1:B12,COUNTIF(B1:B12,"<="&A1)+1)

If B1:B12 sorted...

=INDEX(B1:B12,MATCH(A1,B1:B12,1)+(LOOKUP(A1,B1:B12)<=A1))

 

[Peter_SSs]

curious1

You just need to check what result you want if A1 is exactly equal to a value in column B. My formula and Aladin's produce different results in this circumstance. As well as checking, say, 999 in A1 also check 3499 - if it is possible that A1 can be that value.