Search code examples
excelexcel-formulaxlookupindex-match

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

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.

Solution

  • 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)))

    enter image description here