Numbers index/match function that returns multiple values

=IF(A2≠"",TEXTJOIN(", ",TRUE,REGEX.EXTRACT("~"&TEXTJOIN("~",FALSE,Table 1::A:B)&"~","(?<=~"&A2&"~)(.*?)(?=~)",0)),"")

In this case, it is textjoining the two columns like this: ~Author A~Book 1~Author B~Book 2~ and so on. REGEX.EXTRACT is looking behind for "~Author A~" and ahead for the next "~" and returning the characters between them. If a book is titled "Author A" but was not written by Author A, it would return the next author's name instead of a book title. A book titled "Author A" written by Author A would be okay.

Table 1 column C is not required.

I hope one day a multiple-match function will come out to make multiple matches easier. But there are several ways to do it. Here is one. I can't say I have extensively tested it so let me know if it has any problems.

Add a column to your data table to put the author and book together.

Table 1::C2 =A2&":"&B2

Fill down to complete the column

You can hide this column later

Formula in Table 2::B2 =IF(A2≠"",TEXTJOIN(", ",TRUE,REGEX.EXTRACT("~"&TEXTJOIN("~",TRUE,Table 1::C)&"~","(?<=~"&A2&":)(.*?)(?=~)",0)),"")

Fill down

Explanation (using Author A as the input):

1. It uses TEXTJOIN to join together all the Author::Book strings from Table 1::C with a ~ character between them, plus it puts one in front and back of the entire string.
2. It then uses REGEX.EXTRACT to find all the times "~Author A:any characters~" appears in that textjoined string and makes an array of all the "any characters" it found, which will be the book titles for that author. More precisely it does a lookbehind (?<=~"&A2&":) to find "~Author A:" and does a look ahead (?=~) to find the "~" after the book title and it returns the text between those two things, for each occurrence it finds.
3. Finally it uses TEXTJOIN to join all of those book titles together with a comma between them.

If I could guarantee a book title would not be the same as the name of an author (such as Carl Sagan authoring a book about space and someone else writing a book about him titled titled "Carl Sagan"), the extra column in Table 1 would not be needed and a slightly different formula would be used.

The tilde character "~" is not an acceptable character in a book title or author name. If there are any, you will need to use a different character in the formulas or in the authors/books. The ":" can also be problematic in specific cases but probably not a problem.

If you keep increasing the scope like this, at some point you will have to use another method. I am not an expert on regular expressions (those weird combinations of characters used for the search) but the version used in Numbers does not appear to allow wild cards in a lookahead or lookbehind.

Table 1 C2 =IF(COUNTMATCHES(A2,'Table 1-1'::$A$2),B2,"~")

Fill down

Table 1-1::B2 =SUBSTITUTE(TEXTJOIN(", ",TRUE,Table 1::C),REGEX(", ~|~, "),"")

The formula in Table 1-1 should not have to be this complicated except that there is a bug in Number's version of the TEXTJOIN so some extra work is required. The formula in Table 1 should have been written to result in either the book title or a null string "", not "~". TEXTJOIN should have been able to join the book titles with commas between them, ignoring the null strings, and that should have been the end of it. Unfortunately TEXTJOIN does not ignore null strings so you end up with bunches of repeated commas. The formulas I provided are one way to work around that bug until Apple decides to fix it (unless they already fixed it in versions later than 13.1, the version I have).

Do you really need the books listed elsewhere with commas between the titles or could you do this with actual filters in the data table and no formulas at all?

Hi mkjohnnie,

We are not looking over your shoulder. A screen shot of a small part of table 1 (books and metadata) and another screenshot of a small part of table 2 (authors and metadata) will help us to see what you see.

Are the metadata the lookup values for INDEX and MATCH? Or do the metadata somehow contain lookup values?

More details may lead to a solution.

Regards,

Ian.

To filter your results:

Using the first solution, change the formula in Table 1::C so it results in strings like "Author A:Book 1" for those you want listed and "XXXX:X" or something like that for those you do not. Then use the search formula from my first solution.

Your Table 1 has an "Author A" in column A with the same capitalization and spaces and no extra characters of any kind? If it appears so, try copying the author's name from Table 1 and pasting it into Table 1-1::A2 so you can be sure it is the same and see if that works.

If not that, is the formula in Table 1::C2 correct? Does it give results like in my Table 1 with a ":" directly after the author's name, no added spaces before or after?

If capitalization may be a problem, we can turn the author name into all caps in Table 1::C and in the search formula so it is no longer an issue. If extraneous spaces may be a problem, we can use TRIM to remove them in Table 1::C.