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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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

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

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,583
Members
417,223
Latest member
jelena_

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
Top