# Greater Than Lookup function

#### Jwhalin

##### New Member
Their does not seem to be a way to perform a lookup that provides for returning the nest higher value (in the table). Any example would be an array (table) of box sizes arranged from smallest volume to largest with a part number next to each (as the output of the lookup). Now you would do a vlookup (or whatever) for a given size or volume. The vlookup returns either an exact or one smaller - obviously you can not put more into a lesser box - so why can't it select the next value Greater than the input value rather than equal to or less than....

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hello,

Look into MATCH or INDEX

Welcome to the board.

If your box sizes are in A1:A10 and part numbers in B1:B10 then with your given size in C1 use

=INDEX(B1:B10,MATCH(C1,A1:A10)+(LOOKUP(C1,A1:A10)<>C1))

Thanks guys, Barry that worked great as long as the value in C1 is larger than the smallest value in the array. returna N/A if it is smaller. NOT a problem in my application..... Jeff

either include a zero at the start of the lookup range or amend to

=INDEX(B1:B10,IF(C1< A1,1,MATCH(C1,A1:A10)+(LOOKUP(C1,A1:A10)<>C1)))

Finding this site is a Godsend, I wasted hours trying to figure out how to do this yesterday - and you guy gave me the answers is minutes.... THANKS

i agree with j's sentiments below. thanks for the simple solution, barry -- i was trying all sorts of bad ideas (LARGE, FREQUENCY, you get the picture...) until now.

cheers! ben.

Finding this site is a Godsend, I wasted hours trying to figure out how to do this yesterday - and you guy gave me the answers is minutes.... THANKS

Replies
2
Views
73
Replies
6
Views
638
Replies
1
Views
223
Replies
4
Views
549
Replies
1
Views
1K

1,217,681
Messages
6,137,951
Members
450,100
Latest member
mscetr

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