Finding a range of products that fits within a given area

[chriskay]

Hi all

I want to make a sheet with a list of products (i have a variety of about 1200 products in total) in which the area of the products are also listed.

In the green cells in the attached example sheet, I want to manually input the size of the area that a customer gives me. I need the yellow cells to return a list of all products that fits within the given area (orientation of the products doesn't matter).

Is there any possible way of doing this? I was trying to use the LOOKUP functions, but I can't make it work

[benishiryo]

hi there. maybe this array formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

are your Product really numbers? if they are, use this to prevent zeroes from appearing:

Please Login or Register  to view this content.

if they are texts, use:

Please Login or Register  to view this content.

[chriskay]

Thank you benishiryo! Yes - the products are "numbers" :-)

This formula works as intended:

Please Login or Register  to view this content.

But what if orientation doesn't matter? E.g. if you instead of "700 x 500" enter "500 x 700"?

Are there any way to make this possible too?

[benishiryo]

you're very welcome. but let me get this straight because you don't seem to have an eg showing such a scenario. so say we add in a new data in row 11:
Product 10010
length 400
width 650

this is supposed to be included because the reverse scenario 500 x 700 would include this? if so, try:

Please Login or Register  to view this content.

[chriskay]

I just figured that the easiest way is to sort the product dimensions B: Highest value - C: Lowest value, and then always enter the measured area highest x lowest :-)

Thank you very much for the help!! :-D