Forum Discussion
Return a list of matches across multiple columns based on criteria from Cascading Menus
Long time referencer, first time poster... I feel like I am a well-versed Excel user but have run into a situation I cannot seem to resolve. I wanted to bounce it around and see if anyone has any input that can point me the right direction.
I started a new position at a small school and one of the first things I wanted to do was streamline the way that the department references who has Credit Cards. The initial steps were easy and I made a good file that was working well and compiles data from multiple sources to create a single lookup which returns all the information about the specified card holder:
One tab has all the dropdown menu options, and then the data is compiled on a separate tab spanning multiple columns. This is easy enough and done mostly with =XLOOKUP to return the data - No Problem.
However, it occurred to me that it may be helpful to have a separate tab with the ability to return a list of all people that fit multiple criteria; for example, everyone in Football program (Lookup 'Sport' and Filter by 'Football'):
Up to now, no problem as the Cascading Dropdowns are working well.
I do not know if it is possible, but what I cannot figure out is a function that allows Excel to look at data across multiple columns (Columns C:I in this screen shot) and return a list (Column B) of everyone that matches.
Essentially what I (think) would like is a way to combine the functions of Cascading Dropdowns and XLookup to return all names in a single list.
Any suggestions are appreciated!
3 Replies
May not be the efficient, but I stumbled into a fix.
I attempted your solution m_tarler but it returned an error if a value wasn't selected. I definetly could have errored on my input but your suggestion got me thinking. It may be very clumsy but the following worked...I made a reference table to mirror the fields on the original 'Card Holder' tab that has all the user's data.
Then in each column I used
=UNIQUE(FILTER('Card Holder List'!B4:B212,'Card Holder List'!L4:L212=$D$5))Where B4:B212 is the Card Holders name from the master list, L4:L212 matches the ref table column to the master list, and $D$5 is the second in a cascading dropdown.
Predictably, this returned an error because (in this example) $D$5 does not appear in the L4:L212. Next, I applied this formula across the whole reference table and corresponds to the correct column of the master list. It applies an Error is every column except where the $D$5 matches. And in this case, it returns the Card Holder's name from the Master List -- here the name is test, test.
Next I used the following formula in L6 to return a list of every unique value from the entire Reference Table:
=IFERROR(UNIQUE(VSTACK(W8:W300,S8:S300,T8:T300,U8:U300,V8:V300,R8:R300,X8:X300,Y8:Y300,Z8:Z300,Q8:Q300,AA8:AA300,AB8:AB300,AC8:AC300,AD8:AD300,AE8:AE300,AF8:AF300)),"")It resulted in a short list, but still had some flaws:
Wanting to get rid errors the following formula was then applied to M6:
=UNIQUE(FILTER(L6:L275,L6:L275<>""))This returned a filtered list that consolidated and omitted the errors:
Lastly the following formula was used to now filter out the zeros and placed in the Reference Card (D12) where I wanted to show the information:
=UNIQUE(FILTER(M6:M275,M6:M275<>0))It returned the correct list of every person matching the two criteria from the cascading list:
Lastly, XLOOKUP was used to show in E12 through H12 to return the values based on the listed name (test, test).
=XLOOKUP($D12,'Card Holder List'!$B:$B,'Card Holder List'!J:J)When completed it reads as:
As a test I added more Test names to confirm the function of this work around:
I then did conditional formatting to expand/contract the aforementioned 'Reference Card' so that it always looked clean and ready to print/PDF.
Not sure it's the most direct solution, but it worked and I like that the returned list expands and contracts the 'Reference Card'.
I'm glad you got it to work. My response was very generic in nature as without the sheet and more info it was hard to be more specific. Alternatively a more refined version of my prior could be:
=FILTER( TABLE, FILTER(TABLE, TABLE[#HEADERS] = $B$4) = $B$5, "")
so the inner FILTER pulls only the column that matches $B$4 and then the outter FILTER should pull the rows of the table where that column matches the sub-dropdown.
In case of error values you can wrap that with an IFERROR
=IFERROR( FILTER( TABLE, FILTER(TABLE, TABLE[#HEADERS] = $B$4) = $B$5, "") , "Value not found")
Note that the TABLE can/should be the subset of the table you want to output so you may need to use CHOOSECOLS to select which columns you want from the full table range or HSTACK to 'build up' the set of columns you want.
It sounds like you want to use FILTER
=FILTER( Names, (criteria1)*(criteria2)*(...), "")
those criteria can be simple like Catagory=b2 or searches like ISNUMBER(SEARCH( sportName, SportColumn))
