I am trying to build a database of all the technology resources we have in our company. I would like to have a search table on another tab where a user can narrow down what they are looking for using a few drop down menus. For example, they could choose "Laptop" from the "Equipment" drop down, and the list would populate with all the Laptops in the company. They could then select "Las Vegas" from the "Office" drop down, and it would populate with all the laptops in the Vegas office.
I have been using the FILTER formula with various forms of logic. When I use AND, it's considering the blank cells from the unused drop downs, so it returns #N/A since it can't find any rows matching the selections plus a lot of blank cells. When I use OR, I end up getting nearly everything from the database.
I've tried using both AND and OR together, incorporating IF statements with <>"", as well as various variations incorporating all three of them, but nothing has worked. I'm at a bit of a loss. Is there any way to get FILTER to ignore the cells that are blank?
Link to a sample spreadsheet is here.
Try this in Search!E3:
=FILTER(Inventory!B4:I1000,if($C$3<>"",(Inventory!B4:B1000=$C$3),Inventory!B4:B1000<>$C$3)*if($C$4<>"",(Inventory!C4:C1000=$C$4),Inventory!B4:B1000<>$C$4)*if($C$5<>"",(Inventory!D4:D1000=$C$5),Inventory!B4:B1000<>$C$5)*if($C$6<>"",(Inventory!E4:E1000=$C$6),Inventory!B4:B1000<>$C$6)*if($C$7<>"",(Inventory!F4:F1000=$C$7),Inventory!B4:B1000<>$C$7)*if($C$8<>"",(Inventory!G4:G1000=$C$8),Inventory!B4:B1000<>$C$8)*if($C$9<>"",(Inventory!H4:H1000=$C$9),Inventory!B4:B1000<>$C$9)*if($C$10<>"",(Inventory!I4:I1000=$C$10),Inventory!B4:B1000<>$C$10))