INDEX MATCH Search across workbook

I would like to search 5 unique product sheets (one at a time) through a drop down selection interface on a separate sheet. (see example)


The search is based on 4 input criteria (length, width, height and mass) and match the closest record containing the next largest dimensions across these 4 individual criteria in the selected category.


The caveat being that the search should add +2 to each of the inputs searching.


This is in essence a "nearest neighbor" +2 problem that loops through the selected worksheet returning the next largest matching record by size.




I have a working version in a single worksheet View attachment Package chooser Rev2.xlsx based on the formula:







I'm having difficulty implementing this across multiple worksheets.




The current formula is dependent on a generic “UID”;

I am using the formula to reference a table that has different packages. It only works if you have your package table sorted in descending order on what I am calling the UID (unique ID) column.


Can this be accomplished without the dependence on the “UID”?

Thanks!

First, in the attached in sheet Search Example, cell B23 there's a solution to using the data from different sheets; select the sheet name in B17 and the result in B23 comes from that sheet. I changed the ID column on each sheet so that it's obvious that the data comes from a different sheet. But this still uses your UID.
I hope I'm right in guessing that you use inputs+2 to ensure that you choose a big enough package when doing a MATCH, because if I'm wrong the rest below is a waste of space.
In cell B24 there's another formula that neither uses UID nor inputs+2 to get the right package ID, nor does it need the data table on each sheet to be in any particular order.
It also allows the tables on the different sheets to have different numbers of rows if necessary, and can they can be anywhere on the sheet. This is because I have added a named range to each sheet called myTable, which contains just the data (no headers).
There is one proviso to this working correctly, and that is that in each myTable, the three columns Length, Width and Height, for a given row, should have the largest dimension under Length, the smallest dimension under Height and the middle one under Width, so getting smaller from left to right (of course if the three values are all the same it doesn't matter!).
Conversely, it doesn't matter what order the dimensions are in cells B2020 of the Search Example sheet, the formula sees to that with Max, Min and Median.

There is some commentary in the attached too.
It would be good to see some real data tables and dimensions/weights.

Thanks! This is extremely helpful and much appreciated,

After much exploration and some issues with the cumulative UID method...since the individual product sheets need to remain completely clean and be update-able by other users without breaking anything (or sorting) I am thinking that this will require VBA to properly work.

I'm looking at declaring a variable for LWH+M and then running a "VBA Loop to Find Records Matching Search Criteria" (check YouTube for the video)

I can post some real data tables if you are interested in helping, although this thread now belongs in VBA

digibay said:

I can post some real data tables if you are interested in helping, although this thread now belongs in VBA

Click to expand...

Yes, do post some real data tables, either here or in a new thread in the vba section, but if you do create a new thread somewhere, say so in this thread and (try to) link to it in this thread otherwise I won't know about it.

Was I right about trying to do away with inputs+2?

ps. I think at this site, you may have to have a few more posts to be able to attach a file; if you cant attach a file, then post a link to the file in the cloud, and if you also have difficulty posting a link, miss off the http:// part of the link!

Posted revised question with real data tables in VBA forum!

excelguru.ca/forums/showthread.php?5045-Select-Case-Loop-across-4-conditional-values

also here, here and here.