Filter Dynamically With Multiple Criteria (Without FILTER Function)

Question

I have a dataset with two search fields as shown in the image below. I can use this formula to filter my data without a problem if BOTH search fields are filled.

At the cell J11,

=IFERROR(INDEX($B$11:$D$15, AGGREGATE(15,6,ROW($B$11:$B$15)-ROW($B$11)+1/(($C$11:$C$15=$C$4)*($D$11:$D$15=$C$6)),ROWS($J$11:J11)), COLUMNS($J$11:J11)), "")

But I need the result to show even if I fill only ONE search field. For example, if I enter "A" for Team and leave blank in Gender, then the result should show all players from the team "A", regardless of their gender. If I enter "A" for Team and "Male" for Gender, then the result should show all male players from the team "A".

I can achieve this by using the FILTER function. But because some of my users are still using Excel 2019, I cannot use FILTER in my case.

Could someone help how to make this works (without using the FILTER function)? Thank you in advance.

Answer 1

Like this:

=IFERROR(INDEX($B$11:$D$15, AGGREGATE(15,6,ROW($B$11:$B$15)-ROW($B$11)+1/(IF($C$4="",1,$C$11:$C$15=$C$4)*IF($C$6="",1,$D$11:$D$15=$C$6)),ROWS($J$11:J11)), COLUMNS($J$11:J11)), "")

Answer 2

I never thought of using IF in this case. This is perfect! I use it in my actual worksheet with more search fields and thousand rows of data, everything works fine! Thanks so much, HansV. I really appreciate your help 👍