excel-formulaexcel-2010

Excel Lookup - Find and Compare two Columns and insert data into a third

I have three columns in Excel (not of equal length)

Col B Col C Col D

C contains reference numbers and D contains an ID for the reference number in D, they are matched/paired on the same row.

C D
1234 12345
5678 12346
1357 12347
1369 12348
3694 12349

Column B also contains a list of reference numbers, some of which are in Column C. I should point out that the numbers are not in the same order on Col B and C.

B C D
1234 1234 12345
1369 1369 12348

Can anyone show me a formula to find the records in B that match C and if a match is found put the the adjoining value from D in a new column (A) next to the found record in B?

A B C D
12345 1234 1234 12345
12348 1369 1369 12348

I have tried Vlookup but this formula is a little too complex for me.

Solution

  • VLOOKUP() is the way to go:

    In A1 (or wherever you begin), =VLOOKUP(B1,$C$1:$D$3,2,FALSE)

    Explained:

    B1 is the value you're looking for;

    $C$1:$D$3 is the range in which you will be looking and returning from, note that VLOOKUP will always search in the first column (so C column); Adjust according to your actual range; Now I've used absolute references, so you can just drag down from here on.

    2 is the column number which you wish to return (so D column);

    Solution VLOOKUP

    Wrap the thing in ISERROR(formula here, "error message here") to add an error message if not found:

    Error solution (Column A contains the formula, column B was manual input)

    Edit: added FALSE argument to the formula to find only exact matches, thanks Darren Bartrup-Cook

    Using XLOOKUP()

    =XLOOKUP(B2:B600,$C$2:$C$600,$D$2:$D$600,"not found",0)

    Now B2:B600 are your lookup values (I'm not sure this is already implemented in Excel 2010), otherwhise you should use B2 and drag down.

    C2:C600 is your lookup range (you're looking for column B in column C);

    D2:D600 is your return range, here lies the advantage of XLOOKUP, it can be offset, it can be anywhere on your sheet, instead of in the same matrix/range.

    ""not found"" is an error text, in case the lookup value is not found.

    0 indicates that you want to find exact matches (you can omit this, it's the default for XLOOKUP).

    Note: you could replace (B2:B600,) C2:C600 and D2:D600 with just (B:B) C:C and D:D, although this might make your sheet a bit slower.

    XLOOKUP