Forum Discussion

Joeschae561's avatar
Joeschae561
Copper Contributor
Aug 26, 2021

Need proper function

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.

Formulas and Functions

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 26, 2021

    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:

    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.

Resources