Using UNIQUE and FILTER Functions along with DATA Validation, I'm trying to create a room finder using multi-level dependent DropDown lists in EXCEL 365.
The list where the DATA is being pulled is formatted as a table.
I've been successful up to the 4th DropDown list. Unfortunately, it displays all the rooms for all the floors of the building specified instead of just the rooms available to the floor.
See Screenshot DropDown list with Formulas
Layout of the sheet is as follows: A1:Region | B1:Building | C1:Floor | D1:Room Details
DropDown DATA Validation
A2:List=$A$26# B2:List=$B$26# C2:List=$C$26# D2:List=$D$26#
Table has 4 columns spanning from (A4:D24) with headers: A4:Region | B4:Building | C4:Floor | D4:Room Details A5:D4 is filled with the relevant DATA. Every room listed and is on it's own row, even if it is in the same building and on the same floor.
UNIQUE & FILTER Functions:
A26:=UNIQUE(Details[Region])
B26:=UNIQUE(FILTER(Details[Building],Details[Region]=A2))
C26:=UNIQUE(FILTER(Details[Floor],Details[Building]=B2))
D26:=FILTER(Details[Room Details],Details[Building]=B2,Details[Floor]=C2)
I'm really stumped on this, I've tried to pass it through VLOOKUP, OFFSET, SEARCH, and FIND, but I keep breaking the formula to display either CALC! or VALUE#. I'm not sure what I'm doing wrong, any help is appreciated.
Please see the documentation on FILTER()
Where you will note for multiple criteria we use * in the second criterion. the third is reserved for what if there is no match.
FILTER used to return multiple criteria
In this case, we're using the multiplication operator (
*) to return all values in our array range (A5:D20) that have Apples AND are in the East region:=FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"").
So it should be:
=FILTER(Details[Room Details],(Details[Building]=B2)*(Details[Floor]=C2))