How to match any one of several possible values in a range, and filter matches by another value

Question

Essentially, I am working to compare 3 tables. I know my way around XLOOKUP, FILTER, INDEX, and MATCH but not, apparently, enough to manipulate them the way I'm trying to! If this is something that just needs to be a VBA or Python script, let me know.

1. Is a list of products that are missing from a customer.
2. Is a list of parent products (I'll call them bundles from here on for clarity). Each child product may belong to one or more bundles.
3. Is a list of bundles owned by a specific customer.

Let's call my 3 tables Table1, Table2, and Table3.

• Table1 has 2 columns, A and B, containing a product name and a customer name
• Table2 has 2 columns, A and B, containing a product name and a bundle name
• Table3 has 2 columns, A and B, containing a bundle name and a customer name

I need to find, for each product in Table 1, whether or not the customer owns any bundle that contains the product. I don't need to return a specific match out of the list, just the first one I find for the customer. But, I keep running into situations where I have to nest array formulas inside of other array formulas and can't quite get there on my own.

Here is what I have tried so far:

• FILTER(). I was able to filter the range of bundles to get a list of all possible bundles for a given product like so: =FILTER('Table2'!A:A,'Table2'!B:B='Table1'!A2). But from there it all went downhill...
• Tried to XLOOKUP this list of selected bundles against the customer's bundles like so: =XLOOKUP(FILTER('Table2'!A:A,'Table2'!B:B='Table1'!A2), 'Table3'!A:A, 'Table3'!A:A). This finds me a match for each bundle, but doesn't find me a match that belongs to the given customer in 'Table1'!B2.
• So then I tried the Boolean trick with XLOOKUP to do multiple comparisons: =XLOOKUP(1, (FILTER('Table2'!A:A,'Table2'!B:B='Table1'!A2)='Table3'!A:A) * ('Table3'!A:A=B2), 'Table3'!A:A). Unfortunately now I get a #N/A error.

I tried several more variations of this using INDEX and MATCH instead, using a nested FILTER, but all led to the same dead end. I just can't wrap my head around how to filter a range by another range + some other criteria (the customer name). Can anyone point me the right direction?

EDIT: I tried a new approach, doing a separate filter for bundles that belong to the customer: =FILTER('Table3'!A:A,'Table3'!B:B='Table1'!B2). So now, I have two arrays - one of the possible bundle matches, and another of the bundles the customer owns. But still can't figure out how to inner join those two lists using a formula.

Answer 1

Have used a few parameters in a LET function here

First FILTER the product/bundle table to find which bundles match the product

Next FILTER the bundle/customer table to find which bundles match the customer

Then MATCH the results of the first FILTER against the second FILTER to see if any product bundles match any customer bundles. Wrap that in IFERROR to handle cases where there is no match, and MAX to only get 1 matching bundle.

Then use INDEX to get the name of the matching bundle, and wrap that in IF in case there is no matching bundle.

=LET(PRODBUND,FILTER(Table2[Bund],Table2[Prod]=[@Prod]),
CUSTBUND,FILTER(Table3[Bund],Table3[Cust]=[@Cust]),
MAXBUND,MAX(IFERROR(MATCH(PRODBUND,CUSTBUND,0),0)),
IF(MAXBUND=0,"No match",INDEX(CUSTBUND,MAXBUND)))

Answer 2

Your formula would indicate you are using 3 different sheets, not tables. Here is a solution using tables.

Your first step with FILTER to get a list of matching bundles works fine.

From there you can use COUNTIFS to see how many rows in Table3 match the customer and each bundle.

Finally use SUM and >0 to check if there was any match for any of the bundles.

=SUM(COUNTIFS(Table3[Bund],FILTER(Table2[Bund],Table2[Prod]=[@Prod]),Table3[Cust],[@Cust]))>0