I would like help with a problem, or rather a challenge in Excel and/or Google Sheets.

What we want to develop is as follows:

We have a table of products and certain attributes. Now we want to create a kind of search function based on this table.

• Example:

Let me give a simple example. Suppose you have as a product an apple, a banana and an orange. The characteristics associated with these are size, color country of origin. We then want a search function, where you indicate one or more preferences, i.e. size, color and/or country of origin and that based on those criteria, all products that meet these criteria are displayed. So if you specify oblong as the size and do not specify any other criteria, it only shows "Banana. If the banana and the orange have Holland as their country of origin and you only give Holland as the criteria country of origin, it will show 'Banana' and 'Orange'. If you say country of origin Netherlands and format oblong, it again shows only 'Banana'

See below an image of our document and how we would like this to look approximately.

Currently, there is no existing formula, because we simply do not know if this can be done and how best to do it.

The document can be accessed at:

A copy of our document with sample data:

The raw data is in Jira Issues tab, the data populated is based on multiple filter conditions. I am using some name ranges for the filter values for a better understanding of the formula, such as: startPeriod, endPeriod, etc. You can test the actual query will be invoked looking at the result of the consolidated string of the query input argument of QUERY function.

Similarly you can stablish a where statement to consider whether the input parameter is empty or not. In such case, you can build a logic like this inserting an IF block as part of the where statement and concatenate the string result.

=QUERY(Input!A:Y, 
  "select *" & " where A " & IF(B2="", "<>'*'", "='"&B2&"'")
  "and " & " where B " & IF(B3="", "<>'*'", "='"&B3&"'")  
,1)

The above query for column A or B, returns the entire column via condition: "<>'*'" if the input parameter B2 or B3 were not specified. In a similar way you can add additional conditions for more parameters, repeating the third line of the query and changing the column and the parameter cell.

Recommendations

1. Focus on a specific tool: Excel or Google Spreadsheet, even they have some similarities, you need to get familiar with the specifics of each one of them.
2. Try to start working on your specific problem, once you face impediments, do some research, usually you are not the first person facing this problem, if you don't find a solution, then post your specific problem using a sample as an extract of your real problem (in English, your sample is in other language). Generic questions like this one are difficult to get some attention.