Define a cell with parameters from another cells to create a filter

[marmaries]

Hello everyone!

I want to define a cell with several other cells, so that I can then create a filter.
For example: name of things in the Coloumn A: f.e. “apple" (A1). Column B is for parameters: "sweet", "round", "fruit" (B1, B2 and B3, respectively).

I want to somehow "define" A1 with parameters B1-3, so then to make a filter that will work as follows: for example, selecting the parameter "sweet", I will get values from column A, which have these parameters ("apple", "strawberry", "raspberry", etc.).

I hope I was able to explain what I mean. :D
If so, maybe someone could help me?

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[marmaries]

Dear Ali, dear Glenn,

Tank you very much for pointing out my mistake!

I have attached to my comment what I was trying to explain.

So I want to create a custom filter that will show me the foods that have the parameters I have chosen. I understand that I need to "link" the parameters from column B to the food names from column A so that I can "define" the A cells.
For example: I want the filter to show me all the foods that have the parameters "sweet" and "fruit". To do this, I click on the "Select parameters" drop-down menu, select "Fruit" and "Sweet" and I get "Pear" and "Apple" in the E column.

I hope it was understandable enough.

[Glenn Kennedy]

It's not a mistake... it's just easier to understand.

However, using merged cells is a mistake. The best advice on merged cells is to be found here:

https://excel.solutions/2016/10/usin...ctively-excel/



Without them you can use:
=IFERROR(INDEX(H:H,MATCH(K2,I:I,0)),"")

With them, it's a bit more complicated...
=IFERROR(INDEX(LOOKUP(ROW($A$3:$A$14),ROW($A$3:$A$14)/($A$3:$A$14>0),$A$3:$A$14),MATCH(D2,$B$3:$B$14,0)),"")