Need proper function

Copper Contributor

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.

1 Reply

@Joeschae561 

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:

S0709.png

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.