I have 2 sheets in Excel with two different sets of data but a shared column. I'd like to combine the all the data into 1 sheet, but despite my Googling I'm not sure how to achieve this.

I've tried VLOOKUP() but with no results.

EG in Sheet 1 I have:

=VLOOKUP(A1,Sheet2!B1:B600,1,FALSE)

But it always returns #NAME? (new to Excel, don't know what this means).

Would anyone know how I could achieve this?

My data looks like:

Sheet 1

Sheet 2

Desired outcome

CodePudding user response：

You may want to take a look at the VLookup documentation to see that it is not a good fit for your requirement. Vlookup looks up the lookup value in the 1st column of a range, then returns the value from the specified column. Your formula looks for a text like "Joe" in a column with ID values, but you really want to look up the email address and return the column to the left of that. Vlookup cannot do that.

If you have Office 365, you can use XLookup() instead, like this in Sheet 1:

=xlookup(B1,Sheet2!$B$1:$B$600,Sheet2!$A$1:$A$600,"not found")

in words: use the email in B1, find that email in column B on Sheet 2 and return the value from column A for the found email.