Finding the closest match from 3 corresponding lists

[simonpreston]

Hi Guys,

First time posting here! I have been struggling with this for a while now trying to work it out on my own, but to no avail, so I am hoping someone here can help!

I work for a packaging company, and we sell hundreds of different boxes in different sizes and shapes. On a daily basis, we receive requests from customers giving there required box dimensions, and we have to go through all the products, checking the dimensions, to find the closest match.

I was hoping to create a spreadsheet whereby I can insert the customers required Height in one cell, required Depth in the next cell and required Length in the next cell, and it will perform a search which would bring back the product code with the closest corresponding dimensions. The formula would need to take in to account all 3 criteria.

I have tried using MAX(IF(, but I cannot get it to work across multiple criteria - I am pulling my hair out aha!

I have attached a small example of what I need - Column A is the product code, Column B is the Length, Column C is the With, Column D is the Height - These would be the raw data that would be hidden.

Cell H2 is where myself or a colleague would input the customers required Length, I2 is for the customers required Width and J2 is for the customers required Height. L2 is where I would like the resulting product code to be (where I assume I would input the formula)

If anyone can help that would be AMAZING!

Thank you,

Simon

[tim201110]

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$140)/($H2<=$B$2:$B$140)/($I2<=$C$2:$C$140)/($J2<=$D$2:$D$140),1)),"nomatch")

[AliGW]

... and welcome to the forum!

[simonpreston]

Hi Tim, thank you so much for your reply! It seems to work half of the time, but if you see attached file (I have applied your formula to a working file), from the dimensions of 200 x 200 x 200, it has given me SLC12, which has dimensions of 410 x 310 x 395, however the S6H has dimensions of 330 x 285 x 205, which would be the closest match - Are you able to advise please? Thank you so much again!

[simonpreston]

Thank you!!

[tim201110]

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$140)/($H2<=$B$2:$B$140)/($I2<=$C$2:$C$140)/($J2<=$D$2:$D$140),COLUMNS($K$2:K$2))),"nomatch")

[simonpreston]

Thank you so much, works like a charm

Just out of curiosity, this may not be the right place, but one of my colleagues does not have Excel on his home laptop (ridiculous, I know!) and only has google sheets - It does not seem to work in Google sheets, do you know why that would be? It is coming up as "#NAME?"

Any help would be greatly appreciated!

[AliGW]

It will be one of the functions - a pure guess would be AGGREGATE.