Greater Than Lookup function

Jwhalin

New Member
Joined
Sep 7, 2006
Messages
3
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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))
 
Upvote 0
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
 
Upvote 0
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)))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top