# when you have too many conditions to use IF

#### curious1

##### Board Regular
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

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Like this?

Excel Workbook
ABCD
1789299999
2399
3549
4749
5999
61299
71499
81749
91999
102499
112999
123499
Sheet5

#### curious1

##### Board Regular
*Exactly* like that! Thank you!

##### MrExcel MVP
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

##### MrExcel MVP, Moderator
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.

Replies
1
Views
308
Replies
3
Views
553
Replies
1
Views
506
Replies
4
Views
696
Replies
7
Views
2K

1,191,040
Messages
5,984,289
Members
439,882
Latest member
gerdc

### 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