Greater Than Lookup function

[Jwhalin]

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

 

[onlyadrafter]

Hello,

Look into MATCH or INDEX

 

[barry houdini]

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

 

[Jwhalin]

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

 

[barry houdini]

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

 

[Jwhalin]

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

 

[sweater_vests_rock]

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