Search code examples
excelexcel-formulamatch

Find package number based on list of products

So I have a spreadsheet which shows product packages and products in it

Package Product
Package1 Product1
Package1 Product2
Package1 Product3
Package2 Product1
Package2 Product3
Package3 Product2
Package4 Product1
Package4 Product2

Now I need to backtrace which package was sent to the client given I have a list of products. So if I put somewhere two lines:
Product1
Product2

I need to see result being

  • Package4

It could even list all the packages showing a number of matches for a product and how good the match is

Package # matches Exact?
Package 4 2 EXACT
Package 1 2 NOT EXACT
Package 2 1 NOT EXACT
Package 3 1 NOT EXACT

I was trying to play with VLOOKUP and INDEX/MATCH but couldn't come up with a good result. Ideally This all should happen on the separate sheet where I can enter my products in some designated cell(s) and get results in another cell(s). There are like 180 packages with with 400 products total

Solution

  • One way would be using the newest functions:

    enter image description here

    Formula in F1:

    =LET(A,UNIQUE(A2:A9),B,D2:D3,C,BYROW(A,LAMBDA(a,SUM(COUNTIFS(A2:A9,a,B2:B9,B)))),D,BYROW(A,LAMBDA(a,SUM(COUNTIF(A2:A9,a)))),E,IF((C=D)*(D=COUNTA(B)),"","NOT ")&"EXACT",VSTACK({"Package #","Matches","Exact?"},SORTBY(HSTACK(A,C,E),E,,C,-1,A,)))

    I you don't yet got access to VSTACK() and HSTACK() you can use:

    =LET(A,UNIQUE(A2:A9),B,D2:D3,C,BYROW(A,LAMBDA(a,SUM(COUNTIFS(A2:A9,a,B2:B9,B)))),D,BYROW(A,LAMBDA(a,SUM(COUNTIF(A2:A9,a)))),E,IF((C=D)*(D=COUNTA(B)),"","NOT ")&"EXACT",SORTBY(CHOOSE({1,2,3},A,C,E),E,,C,-1,A,))

    The downside is that this is now without headers.