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.

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.

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?

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

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);

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

(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.