How can I search rows in a Google Sheet using dynamically applied filters? Here is a simple example of what I mean:
I have a scenario that searches two sheets. The first sheet contains search criteria which will be used to search the second sheet.
In the first sheet, the first column contains an email address to identify a user, while the other columns contain search parameters that specify which features the user requires in a lake to live on. A criteria with a value of 1 means the lake must have the feature, a value of 0 means the lake must NOT have the feature, and a blank value means it doesn’t matter if the lake has the feature.
By searching the sheet for an email address (firstname.lastname@example.org) we find Bandit’s search criteria. Bandit does NOT want a lake that allows boats to waterski (a full rec lake) because the waves make canoeing difficult. He doesn’t care if the bottom is sandy or if the lake is large. However, he wants the water to be clear and deep, and he wants there to be a bar/restaurant on the lake he can boat to.
The second sheet contains a list of lakes and their features. Searching the sheet to find which lakes match Bandit’s requirements should result in rows that include Bass Lake, Brekk Lake, and Fish Lake.
Unfortunately, if I filter the second sheet using values in the first sheet, it returns no results. The problem is how to filter when the values in the first sheet are blank because it doesn’t matter if the feature is present or not. In this case, the Sandy and Large fields in the first sheet are empty, so the filter will only return rows where the corresponding lake columns are empty.
Does anyone have advice on getting the scenario to return the lakes that correctly match Bandit’s requirements?