Use Filter Function + Wildcards + List (Named Range)

[zero269]

Hello,

I'm not having any luck using wildcards to return values from a table of data based on a list in a Named Range.

I'm using the following to return a list of books that are part of a given Series. However, due to multiple values in the Series column, I need to be able to use wildcards. In my sample data, we can see that nothing was returned for the "Notebook of Doom" series. That's because the source data has either of the two values shown to the right of the Sample Data.
I tried modifying the search criteria using "*" & l_SeriesActive & "*" to no avail.

Any help would be greatly appreciated, Thank you...

Reading Tracker.xlsm
	A	B	C	D	E	F
1	SAMPLE DATA					NAMED RANGE
2	Quiz	Book No.	Title	Series		Series
3	122343	1	Saving Mister Nibbles	Elliot's Park;		Elliot's Park;
4	123891	2	Haunted Hike	Elliot's Park;		Notebook of Doom;
5	129963	3	Walnut Cup	Elliot's Park;		Trouble at Table 5;
6	507302	1	Trouble at Table 5: The Candy Caper	Trouble at Table 5;
7	507301	2	Trouble at Table 5: Busted by Breakfast	Trouble at Table 5;		Need Wildcards to return:
8	511879	3	Trouble at Table 5: The Firefly Fix	Trouble at Table 5;		Notebook of Doom; Branches;
9	511878	4	Trouble at Table 5: I Can't Feel My Feet	Trouble at Table 5;		Branches; Notebook of Doom;
10	519164	5	Trouble at Table 5: Trouble to the Max	Trouble at Table 5;
11	520203	6	Trouble at Table 5: Countdown to Disaster	Trouble at Table 5;
Series

Cell Formulas
Range	Formula
A3:D11	A3	=LET(x,FILTER(CHOOSECOLS(t_Books,1,23,2,5),COUNTIFS(l_SeriesActive,t_Books[Series])),SORT(x,{4,2}))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
l_SeriesActive	=t_SeriesActive[Series]	A3

 

[Alex Blakenburg]

I can't test this since I don't have the tables set up but hopefully I have all the brackets in the right places.

=LET(x,FILTER(CHOOSECOLS(t_Books,1,23,2,5),BYROW(--ISNUMBER(SEARCH(TOROW(l_SeriesActive),t_Books[Series])),LAMBDA(r,SUM(r )))),SORT(x,{4,2}))

 

[zero269]

I can't test this since I don't have the tables set up but hopefully I have all the brackets in the right places.

=LET(x,FILTER(CHOOSECOLS(t_Books,1,23,2,5),BYROW(--ISNUMBER(SEARCH(TOROW(l_SeriesActive),t_Books[Series])),LAMBDA(r,SUM(r )))),SORT(x,{4,2}))

Hi Alex,

All I can say is WOW! and THANKS a Million!

I really wish I understood all this better. I was completely off.
This is really a big help and will help make tracking the Series for each child so much easier!

Here are the results using your solution which now includes all the books in the "Notebook of Doom" Series:

Reading Tracker.xlsm
	A	B	C	D
15	Alex Blakenburg SOLUTION
16	Quiz	Book No.	Title	Series
17	161191	2	Day of the Night Crawlers	Branches; Notebook of Doom;
18	165105	4	Chomp of the Meat-Eating Vegetables	Branches; Notebook of Doom;
19	179852	9	Rumble of the Coaster Ghost	Branches; Notebook of Doom;
20	183263	10	Snap of the Super-Goop	Branches; Notebook of Doom;
21	187209	11	Sneeze of the Octo-Schnozz	Branches; Notebook of Doom;
22	122343	1	Saving Mister Nibbles	Elliot's Park;
23	123891	2	Haunted Hike	Elliot's Park;
24	129963	3	Walnut Cup	Elliot's Park;
25	159140	1	Rise of the Balloon Goons	Notebook of Doom; Branches;
26	162093	3	Attack of the Shadow Smashers	Notebook of Doom; Branches;
27	169889	5	Whack of the P-Rex	Notebook of Doom; Branches;
28	172162	6	Pop of the Bumpy Mummy	Notebook of Doom; Branches;
29	173661	7	Flurry of the Snombies	Notebook of Doom; Branches;
30	176730	8	Charge of the Lightning Bugs	Notebook of Doom; Branches;
31	189903	12	March of the Vanderpants	Notebook of Doom; Branches;
32	193030	13	Battle of the Boss-Monster	Notebook of Doom; Branches;
33	507302	1	Trouble at Table 5: The Candy Caper	Trouble at Table 5;
34	507301	2	Trouble at Table 5: Busted by Breakfast	Trouble at Table 5;
35	511879	3	Trouble at Table 5: The Firefly Fix	Trouble at Table 5;
36	511878	4	Trouble at Table 5: I Can't Feel My Feet	Trouble at Table 5;
37	519164	5	Trouble at Table 5: Trouble to the Max	Trouble at Table 5;
38	520203	6	Trouble at Table 5: Countdown to Disaster	Trouble at Table 5;
Series

Cell Formulas
Range	Formula
A17:D38	A17	=LET(x,FILTER(CHOOSECOLS(t_Books,1,23,2,5),BYROW(--ISNUMBER(SEARCH(TOROW(l_SeriesActive),t_Books[Series])),LAMBDA(r,SUM(r )))),SORT(x,{4,2}))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
l_SeriesActive	=t_SeriesActive[Series]	A17

I just realized that this will help me identify the Series values, so I can replace all the "Branches; Notebook of Doom;" to "Notebook of Doom; Branches;" so the Sorting is good to go.

Reading Tracker.xlsm
	A	B	C	D
15	Alex Blakenburg SOLUTION
16	Quiz	Book No.	Title	Series
17	122343	1	Saving Mister Nibbles	Elliot's Park;
18	123891	2	Haunted Hike	Elliot's Park;
19	129963	3	Walnut Cup	Elliot's Park;
20	159140	1	Rise of the Balloon Goons	Notebook of Doom; Branches;
21	161191	2	Day of the Night Crawlers	Notebook of Doom; Branches;
22	162093	3	Attack of the Shadow Smashers	Notebook of Doom; Branches;
23	165105	4	Chomp of the Meat-Eating Vegetables	Notebook of Doom; Branches;
24	169889	5	Whack of the P-Rex	Notebook of Doom; Branches;
25	172162	6	Pop of the Bumpy Mummy	Notebook of Doom; Branches;
26	173661	7	Flurry of the Snombies	Notebook of Doom; Branches;
27	176730	8	Charge of the Lightning Bugs	Notebook of Doom; Branches;
28	179852	9	Rumble of the Coaster Ghost	Notebook of Doom; Branches;
29	183263	10	Snap of the Super-Goop	Notebook of Doom; Branches;
30	187209	11	Sneeze of the Octo-Schnozz	Notebook of Doom; Branches;
31	189903	12	March of the Vanderpants	Notebook of Doom; Branches;
32	193030	13	Battle of the Boss-Monster	Notebook of Doom; Branches;
33	507302	1	Trouble at Table 5: The Candy Caper	Trouble at Table 5;
34	507301	2	Trouble at Table 5: Busted by Breakfast	Trouble at Table 5;
35	511879	3	Trouble at Table 5: The Firefly Fix	Trouble at Table 5;
36	511878	4	Trouble at Table 5: I Can't Feel My Feet	Trouble at Table 5;
37	519164	5	Trouble at Table 5: Trouble to the Max	Trouble at Table 5;
38	520203	6	Trouble at Table 5: Countdown to Disaster	Trouble at Table 5;
Series

Cell Formulas
Range	Formula
A17:D38	A17	=LET(x,FILTER(CHOOSECOLS(t_Books,1,23,2,5),BYROW(--ISNUMBER(SEARCH(TOROW(l_SeriesActive),t_Books[Series])),LAMBDA(r,SUM(r )))),SORT(x,{4,2}))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
l_SeriesActive	=t_SeriesActive[Series]	A17

 

[Alex Blakenburg]

Thanks for the comprehensive feedback. If you want an explanation here is the 6 minute video I got it from.