Search with multiple criteria across multiple worksheets

[FergusC]

Something that I have been working on for a while, I feel like I'm getting closer but not quite there! I would prefer a solution without VBA if possible...

In essence I have a number of products which are used for a number of projects in a number of locations. My spreadsheet has a series of worksheets, one for each product, which gives various information on the product including a table of locations where the product has been used, and on what projects at that location.

What I am trying to produce is a search function where a user can input a location and a project number another cell then returns the product used. The product is unique to each location and project combination.

Currently the list of projects for each location is comma separated, slightly complicating the situation. I currently have the below formula which searches the first product page and returns the correct result.

{=INDEX(Product1,MATCH([Location]&TRUE,Product1[Location]&ISNUMBER(SEARCH(","&[Project]&",",","&Product1[Projects]&",")),0),4)}

I am looking for a solution similar to this that searches all of the product sheets and returns the product name when found. I have tried the below but get a #N/A result, evaluation of the formula shows a #VALUE error in the ISNUMBER element of the formula.

{=INDEX(Sheet_List,MATCH([Location]&TRUE,Sheet_List&ISNUMBER(SEARCH(","&[Project]&",",","&Sheet_List&",")),0),4)}

Sample book attached. Thanks in advance!

 

[r2c2]

@FergusC
No wonder you have been searching for sometime. This is a very tricky problem.

I could solve it with two helper columns, one for finding which table has the data and another for which row has the data. Once we know both values, printing corresponding product name is easy.

Let's assume your search inputs are
B15 for Location
C15 for Project

Formula to find the table goes in to cell D15 and reads like this. (array formula)

=SUM(MMULT(TRANSPOSE(sheet_nums),IFERROR(SEARCH("*"&C15&"*"&B15,T(INDIRECT(ADDRESS(TRANSPOSE(ROW($A$4:$A$5)),3,,,Sheet_List)))&T(INDIRECT(ADDRESS(TRANSPOSE(ROW($A$4:$A$5)),2,,,Sheet_List)))),0)))

I have added an extra range called sheet_nums that is just running numbers 1,2,3...

The range A4:A5 is used to get running numbers 1 & 2. Change this according to the size of your actual data. It should work even if each table has different number of rows.

Formula to find the row where match for location & project occurs in the table goes like this: (also an array formula), goes in to E15

=MATCH("*"&C15&"*"&B15,INDEX(INDIRECT("Product"&D15),,2)&INDEX(INDIRECT("Product"&D15),,1),0)

Note that we are using the table number calculated in D15 in the above formula to access the table columns.

Finally, formula to get the product name:
=INDIRECT(ADDRESS(3+E15,5,,,"Product"&D15))

We are adding 3 because all your data starts from Row #4. Please change this number according to original data structure.

My suggestion:
If you have power query, just combine all tables in to one and use that to run these lookups with simple INDEX+MATCH formulas. Saves you a lot of time and effort.

If you don't have PQ, just use VBA (or manual effort) to combine the data in to one big table.

All the best.