Pull names from large list based on category

I am trying to find a formula that will search a table for a category, and then provide a name from the same row in a different column so as to populate a list of of names that match the category.

I have tried XLOOKUP but it just populates the name name that is 1st in alphabetical order, how can I generate a list that will not populate duplicates?

Thanks

Posted on Sep 27, 2023 6:22 AM

Reply
3 replies

Sep 27, 2023 1:42 PM in response to st-8

A lookup will return the first match it finds. Getting a list of all matches takes a little more work. Here is one way (though it does not look for duplicates). Alternatively, I believe a pivot table can do it without any formulas and listing only distinct matches, no duplicates. SGIII can present the pivot table method, I've not used them much.


The top table needs at least one header row for the method shown to work.

Formula in column C of the top table is =ROW()

Put that in all rows of the column


In the bottom table,

Type the number 1 into cell B1

Fill column A with the category name you want he results for. You need enough rows to get them all.

B2 =IF(B1>0,MINIFS(Data::C,Data::A,$A2,Data::C,">"&B1),B1−1)

C2 =IFS(B2>0,INDEX(Data::B,B2),B2=0,"END OF LIST",TRUE,"")

Fill down with the formulas in B2 and C2


You can put a filter on column B to show only those rows that are >0 or >=0 depending on whether you want "end of list" to be shown.


Hide column C in the top table and column B in the lower table.





Pull names from large list based on category

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.