Need proper function

%3CLINGO-SUB%20id%3D%22lingo-sub-2689815%22%20slang%3D%22en-US%22%3ENeed%20proper%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2689815%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20worksheet%20for%20electronic%20parts.%26nbsp%3B%20Each%20part%20has%20various%20Part%20numbers%20dependent%20on%20the%20supplier.%26nbsp%3B%20The%20part%20number%20is%20shown%20in%20one%20column%20with%20alternative%20part%20numbers%20in%20the%20next%202%20columns.%26nbsp%3B%20I%20want%20to%20be%20able%20to%20enter%20one%20of%20the%20part%20numbers%20and%20have%20is%20find%20the%20alternative%20numbers%20and%20the%20provide%20my%20internal%20part%20number.%26nbsp%3B%20I%20have%20tried%20using%20VLookup%2C%20but%20it%20only%20allows%20the%20lookup%20to%20work%20on%20one%20column.%26nbsp%3B%20I%20need%20to%20have%20it%20look%20at%20all%20three%20columns%20and%20the%20give%20me%20our%20internal%20corresponding%20part%20number.%26nbsp%3B%20Any%20ideas%3F%26nbsp%3B%20I%20could%20not%20get%20XLookup%20to%20work%20either.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2689815%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2689865%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20proper%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2689865%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1138797%22%20target%3D%22_blank%22%3E%40Joeschae561%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20hope%20that%20the%20part%20numbers%20are%20unique%2C%20i.e.%20part%20number%20A%20does%20not%20refer%20to%20one%20part%20from%20supplier%201%20but%20to%20another%20part%20from%20supplier%202.%3C%2FP%3E%0A%3CP%3EYou%20could%20use%20something%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0709.png%22%20style%3D%22width%3A%20683px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F306092i5011FFA5594EDF3E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0709.png%22%20alt%3D%22S0709.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20G2%20is%3C%2FP%3E%0A%3CP%3E%3DINDEX(%24A%241%3A%24A%2431%2CSUMPRODUCT((%24A%242%3A%24C%2431%3DG1)*ROW(A2%3AC31)))%3C%2FP%3E%0A%3CP%3EFor%20this%20example%2C%20the%20list%20and%20formula%20are%20on%20the%20same%20sheet%2C%20but%20that%20is%20not%20essential.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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.