We have two spreadsheets both containing a product (the same in both spreadsheets) and a serial number. We'd like to compare both of these, naturally based on product and output all the serial numbers that are found in spreadsheet 2 for one particular product. The product can be found in both spreadsheets multiple times.
Example, where I highlighted one particular product:
Spreadsheet 1:
Spreadsheet 2:
The result that we need is if the product exists in both spreadsheets, output all the serial numbers that are in spreadsheet 2 for that particular products. I tried VLOOKUP, but only get the first match, not all.
The issue is we accidentally lost the leading zeros (could be one or multiple, we don't know) in spreadsheet 1. Spreadsheet 2 contains the correct ones for that product. Additionally if the serial number in spreadsheet 1 is the same as in spreadsheet 2, we don't need that in the result (if that doesn't make it too hard).
We're using Excel 2019, but have access to O365 if that helps, but we'd like to use formulas only if at all possible.
Example, where I highlighted one particular product:
Spreadsheet 1:
Spreadsheet 2:
The result that we need is if the product exists in both spreadsheets, output all the serial numbers that are in spreadsheet 2 for that particular products. I tried VLOOKUP, but only get the first match, not all.
The issue is we accidentally lost the leading zeros (could be one or multiple, we don't know) in spreadsheet 1. Spreadsheet 2 contains the correct ones for that product. Additionally if the serial number in spreadsheet 1 is the same as in spreadsheet 2, we don't need that in the result (if that doesn't make it too hard).
We're using Excel 2019, but have access to O365 if that helps, but we'd like to use formulas only if at all possible.
