How to Create Dynamic List with Name lookup using OR logic

[BrettOlbrys1]

I have data in columns A thru Z with 10,000 rows of data in the table. I created a drop-down list of people's names in a second sheet and when the person selects their name, I want only their data to display in the results. But, their name can appear in columns (J, K, L, or M).

I need a formula that will return all rows of data when the person selects their name, even though the lookup of the name will be across columns J, K, L, M?

Thanks

Brett

 

[Fluff]

How about

=FILTER(Sheet7!A2:Z10000,BYROW(Sheet7!J2:M10000,LAMBDA(br,SUM(--(B2=br))>0)))

Change sheet name to suit & also change B2 to where your drop down is

 

[BrettOlbrys1]

=FILTER(Sheet7!A2:Z10000,BYROW(Sheet7!J2:M10000,LAMBDA(br,SUM(--(B2=br))>0)))

Works great, thank you. One last question, if I want the resulting list to be sorted by DATE from oldest to newest, how do I accomplish that? The DATE is column B.

 

[Fluff]

Try

=SORT(FILTER(Sheet7!A2:Z10000,BYROW(Sheet7!J2:M10000,LAMBDA(br,SUM(--(B2=br))>0))),2)

 

[BrettOlbrys1]

Try

=SORT(FILTER(Sheet7!A2:Z10000,BYROW(Sheet7!J2:M10000,LAMBDA(br,SUM(--(B2=br))>0))),2)

All I can say is YOU ARE AMAZING! Thank you! Works PERFECTLY.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[BrettOlbrys1]

All I can say is YOU ARE AMAZING! Thank you! Works PERFECTLY.

Actually, one last question. If I want to create a filter to limit the list by allowing someone to input a DATE in a blank cell that the formula references, how do I modify the formula? For example, if someone wants to limit the results to everything greater than 1/1/23, but then someone else wants to limit it to greater than 6/1/23. I assume I will have an input cell the formula will reference where the limiting date goes, but what would that formula look like?

 

[Fluff]

How about

=SORT(FILTER(Sheet7!A2:Z10000,(BYROW(Sheet7!J2:M10000,LAMBDA(br,SUM(--(B2=br))>0)))*(Sheet7!B2:B10000>=C2)),2)

 

[BrettOlbrys1]

How about

=SORT(FILTER(Sheet7!A2:Z10000,(BYROW(Sheet7!J2:M10000,LAMBDA(br,SUM(--(B2=br))>0)))*(Sheet7!B2:B10000>=C2)),2)

Thanks Again!

 

[Fluff]

My pleasure.