Aug 26 2021 01:19 PM
I have a worksheet for electronic parts. Each part has various Part numbers dependent on the supplier. The part number is shown in one column with alternative part numbers in the next 2 columns. I want to be able to enter one of the part numbers and have is find the alternative numbers and the provide my internal part number. I have tried using VLookup, but it only allows the lookup to work on one column. I need to have it look at all three columns and the give me our internal corresponding part number. Any ideas? I could not get XLookup to work either.
Aug 26 2021 01:32 PM
I hope that the part numbers are unique, i.e. part number A does not refer to one part from supplier 1 but to another part from supplier 2.
You could use something like this:
The formula in G2 is
=INDEX($A$1:$A$31,SUMPRODUCT(($A$2:$C$31=G1)*ROW(A2:C31)))
For this example, the list and formula are on the same sheet, but that is not essential.