Compare two rows for partial or full match

[Thaleus]

Is there a way to have Excel compare cells in a row to cells in another row (on a different sheet or excel file) to look for a partial or full match and return the row “name”? Example below. One file lists the possible packages and what is contained in those packages (Package A has a nut, bolt, widget, and spacer) a separate file or sheet contains the order from a customer. The contents of the order are compared to the contents of all of the packages, and any rows that contain all of the contents of the order are returned as “if you order this you get everything you need”. Trying to figure this out has me stumped…. Any help is really, really appreciated!

[TABLE="width: 407"]
<colgroup><col span="5"><col></colgroup><tbody>[TR]
[TD="colspan: 5"]Available packages[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pkg A[/TD]
[TD]nut[/TD]
[TD]bolt[/TD]
[TD]widget[/TD]
[TD]spacer[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pkg B[/TD]
[TD]spacer[/TD]
[TD]bolt[/TD]
[TD]nut[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pkg C[/TD]
[TD]bolt[/TD]
[TD]nut[/TD]
[TD]widget[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pkg D[/TD]
[TD]bolt[/TD]
[TD]nut[/TD]
[TD]spacer[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Customer Order[/TD]
[TD](Goal)[/TD]
[/TR]
[TR]
[TD]Order 1[/TD]
[TD]bolt[/TD]
[TD]nut[/TD]
[TD]spacer[/TD]
[TD][/TD]
[TD]Pkg D[/TD]
[/TR]
[TR]
[TD]Order 2[/TD]
[TD]bolt[/TD]
[TD]widget[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pkg A, Pkg C[/TD]
[/TR]
[TR]
[TD]Order 3[/TD]
[TD]spacer[/TD]
[TD]o-ring[/TD]
[TD]nut[/TD]
[TD][/TD]
[TD]n/a[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rijnsent]

Hi Thaleus,
that's quite an interesting puzzle. I'd go for the easiest route and add some helper colums after your orders, checking for every Pkg if it has all the items (one Pkg per column). Trying to get it all in one cells will create one big unreadable array function.
Something like this, matching the order items in B8:D8 with Pkg A in B2:E2:
=MIN(IF(D8<>"",IFERROR(MATCH(D8,$B$2:$E$2,0),0),1),IF(C8<>"",IFERROR(MATCH(C8,$B$2:$E$2,0),0),1),IF(B8<>"",IFERROR(MATCH(B8,$B$2:$E$2,0),0),1))

Two sites that help with more complex formula:
https://www.get-digital-help.com/20...t-string-and-return-multiple-adjacent-values/
https://exceljet.net/formula/cell-contains-all-of-many-things

Cheers,

Koen