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!
<colgroup><col span="5"><col></colgroup><tbody>
</tbody>
Available packages | |||||
Pkg A | nut | bolt | widget | spacer | |
Pkg B | spacer | bolt | nut | ||
Pkg C | bolt | nut | widget | ||
Pkg D | bolt | nut | spacer | ||
Customer Order | (Goal) | ||||
Order 1 | bolt | nut | spacer | Pkg D | |
Order 2 | bolt | widget | Pkg A, Pkg C | ||
Order 3 | spacer | o-ring | nut | n/a |
<colgroup><col span="5"><col></colgroup><tbody>
</tbody>