Find Unmatched Values

Question

Edited to try to clarify a convoluted process that an NDA prevents me from describing in as much detail as I would like.

My employer will sometimes run out of inventory for older products, but a specific unit may still be under warranty or a decision is made to swap it out in the name of customer service. In such cases the customer's unit will be substituted with the closest analog still available.

So, say a customer has a 2018 model television, bought an extended warranty, but it being 2022 there are no longer any parts available for 2018 models. In this case the company swaps out the 2018 model for a 2020 model which is the closest analog they have in stock, but the ERP system won't allow that transaction to take place without some kind of manual intervention, which is where I come in.

Sometimes these substituted products are not part of the same product hierarchy, so an analysis needs to be conducted to determine if edits need to be made to the system to allow the substituting of two products from different product hierarchies.

However, there is no direct mapping between part numbers and product hierarchy values, and an intermediary value (configuration value) is needed to link the two. Also, any given part number could have multiple configuration and product hierarchy values, and I need to make sure these are all accounted for, however I am currently running into an issue where VLOOKUP only returns the first match it finds.

To do my analysis I have an Excel workbook with three worksheets. The first worksheet matches configuration values to product hierarchies. The second worksheet matches part numbers to config values. The third worksheet is to compare the product hierarchy values of two different part numbers (whatever the customer has vs what they're swapping it out for) and tells me if the values match or not.

The first worksheet maps configuration values with product hierarchy values and looks something like the following.

Config Value	Product Hierarchy
Config1	PH1
Config2	PH3
Config3	PH3
Configx	PHx

The second worksheet maps the part numbers to configuration values and then also brings in the product hierarchy value for the configuration value using a VLOOKUP formula and looks something like the following.

Part Num	Config	Prod Hierarchy
PartNum1	Config1	PH1
PartNum1	Config1	PH1
PartNum1	Config2	PH2
PartNum2	Config3	PH3

The third worksheet compares two part numbers and their corresponding product hierarchy values to determine if the product hierarchy values match, using a VLOOKUP formula and looks something like the following.

Part Num1	Prod Hierarchy1	Part Num2	Prod Hierarchy2	PH Match?
PartNum1	PH1	PartNum2	PH3	Yes
PartNum1	PH1	PartNum2	PH3	No

Problem: VLOOKUP only returns the first match it finds. So if PartNum1 has say 2 different product hierarchy values associated with it, VLOOKUP only ever returns the first one. I need something that provides similar functionality to a for loop that will make sure I get all unique mappings of part numbers and product hierarchies.

Using Excel 16.6 (Office 365) on macOS so MS Access is not an option. The ideal solution would be using Excel formulas over VBA, as code does not always seem to work the same as on Windows.