I have a dataset on an Excel table called “data”.
In another sheet, I have two rows, with titles of headers of “data”. Something like this:
A B
1 id:
2 name:The idea is that on B1 and B2, the user types the criteria to search for, and below that, using the FILTER formula, Excel shows the results.
I’m using something like this for one criteria:
=FILTER(data, (data[id]=B1), “No records found”)For multiple criteria, I’m using this:
=FILTER(data, (IF(ISBLANK(B1), "", (data[id]=B1)))*(IF(ISBLANK(B2), "", (data[name]=B2))), "No records found")Both of them work, however, if I use the multiple criteria formula, and B1 or B2 is empty, the FILTER formula comes up as #VALUE!, instead of showing only the information that meets B1 or B2.
Basically, the idea is to use the FILTER formula and show the data, according to whatever column (id or name) the user chooses to search for, or both of them.
2 Answers
When both cells B1 and B2 are not empty and match, the multiple condition formula you use will display the corresponding data.
But consider that the conditions of B1 and B2 come from different series. I suggest you use two FILTER formulas to achieve this. In cell F1 and F2:
=IF(ISBLANK(E1),"",FILTER(data,data[id]=E1,"No records found"))
=IF(ISBLANK(E2),"",FILTER(data,data[name]=E2,"No records found"))Your difficulty here is that you are building two arrays of TRUE/FALSE values and then multiplying the two arrays by each other. The idea would be that if BOTH ID and Name match each other, corresponding places in the arrays will be 1's when multiplied giving you a 1 in the output, and therefore getting the appropriate matching row or rows in the Data Table.
This does not work when there is a blank in B1 or B2 because when either happens, its part of the formula does not produce an array of TRUE/FALSE values. Instead, it produces a single "constant" as its output, the "" or empty cell value.
So the multiplication fails when either is blank.
You can solve this by replacing each of the two "" bits in your formula with FALSE:
=FILTER(Data, (IF(ISBLANK(B1), FALSE, (Data[ID]=B1)))*(IF(ISBLANK(B2), FALSE, (Data[Name]=B2))), "No records found")That way an actual array, all being FALSE, is produced and the multiplication can happen as you intend.
Happy days!
