Hello, dear Community. I've looked for this answer on the forum, but there was nothing found. If this topic was solved some time ago, please give me the link.
Pretty simple question. Xlookup works only for first found reliable answer, but cannot output many reliable answers. (Table 2, B2 is an example.) Please help.
Mac mini, macOS 12.4
There are a number of different solutions. Here is one. It makes a list of the names in a column. It will not put commas between them. That would be an additional step requiring concatenation.
Formula in Table 1 C2 =B2&"-"&COUNTIF(B$2:B2,B2)
Fill down to complete the column.
Note that this formula is NOT sort-safe. There is an annoying and longstanding bug in Numbers that messes up the cell references in the formula if the table is sorted. I hope one day they see fit to fix it. The sort-safe version of the formula is more complicated.
Formula in Table 2 B2 =XLOOKUP(B$1&"-"&ROW()−1,Table 1::$C,Table 1::$A,"",0)
Fill down and across to complete the table.
A few other ideas are in this recent thread: Index and Match with arrays - Apple Community
Other ideas are scattered in the forum over the past many years. I never have much luck searching but a search for "multiple matches" or "multiple lookups" or "multiple results" or something along those lines might yield additional ways to do it.
There are a number of different solutions. Here is one. It makes a list of the names in a column. It will not put commas between them. That would be an additional step requiring concatenation.
Formula in Table 1 C2 =B2&"-"&COUNTIF(B$2:B2,B2)
Fill down to complete the column.
Note that this formula is NOT sort-safe. There is an annoying and longstanding bug in Numbers that messes up the cell references in the formula if the table is sorted. I hope one day they see fit to fix it. The sort-safe version of the formula is more complicated.
Formula in Table 2 B2 =XLOOKUP(B$1&"-"&ROW()−1,Table 1::$C,Table 1::$A,"",0)
Fill down and across to complete the table.
A few other ideas are in this recent thread: Index and Match with arrays - Apple Community
Other ideas are scattered in the forum over the past many years. I never have much luck searching but a search for "multiple matches" or "multiple lookups" or "multiple results" or something along those lines might yield additional ways to do it.
Thank you!
Numbers: Function that returns many reliable answers.
