Excel: Returning Value from third column if three match

Question

I have two excel spreadsheets, with a structure as following:

Sheet1

A1-A1000 (Names):
Doe, John
Example, Jane

C1-C1000 (Birthdates):
12-25-1990
1-01-2000

Sheet2

B2-B1000 (Names):
Doe, John
Matthews, Andy

D2-D1000 (Birthdates):
Matching Birthdates to be populated here

Essentially, I need to compare thousands of values in the two sheets, and if their rows they have matching names, I need to put the birthdate from the row of Sheet1 to the corresponding name into the D column in Sheet2. Anyone have any idea of going about this?

Thank you for your help, I have no experience with excel so I'm new to all of this.

Answer 1

Please take a look at the vlookup function. You can find documentation here: VLOOKUP function

I have put this into Sheet1 (with column B blank):

John        12/25/1990
Jane        1/1/2000

In Sheet2, I placed John, Andy, and Jane in A1, A2, and A3.

In B2, to the right of John, I placed:

=VLOOKUP(A1,Sheet1!$A$1:$C$4,3,FALSE)

Let me explain. The A1 means I'm seeking John in A1 of Sheet2. The

Sheet1!$A$1:$C$4

tells the function to look at Sheet1, columns A through C (and rows 1 through 4). The dollar signs are necessary to tell Excel to use absolute (rather than relative) references. (Relative references would make the whole thing shift in undesirable ways.) (You would want yours to go from $A$1 to $C$1000 if you have 1000 names and birthdays.)

The 3 means to return the third column, which in your case, is the birthday.

The FALSE means that you have to have an exact match.

I then copied down B1 to B2 and B3.

Here are the results:

John    12/25/1990
Andy    #N/A
Jane    1/1/2000

It found John and Jane, and returned their birthdates. It did not find Andy, and so it displays an #N/A.

(Following are edit 2, from comments in which the OP asked some branching questions.)

Copying the formula, using Fill Down

Rather than Ctrl-C + Ctrl-V, there are keyboard shortcuts to copy the formula down a column. Click on B1 to highlight it. Then hold the shift key down and press the down arrow twice. Now B1, B2, and B3 are highlighted. Then press Ctrl-D to Fill Down the formula. If done correctly, one should have the same formula in B3 as in B1. You can reuse this same technique, but you can use Shift+PageDn ten times (and then Ctrl-D) to copy the formula 250 times.

Formatting a column to be dates

Click on the column with the numbers, and change the format drop-down from General to Short Date. This will switch the formatting of the cell from e.g., 21567 (the internal Excel representation) to 1/17/1959.

Adding an error message

In the comments, the OP asked how to have an error message instead of the #N/A. One could wrap the VLOOKUP in another function, namely IFNA. For example,

 =IFNA(VLOOKUP(A1,Sheet1!$A$1:$C$4,3,FALSE), "Lookup failure.")

would provide the date (if the name were found) or would say "Lookup failure."

Answer 2

You are on the right track but you have your columns in the wrong place.

The match is what you use to find the match, so it should be:

MATCH(Sheet2!$B2,Sheet1!$A$2:$A$1000, 0)

Or you can use full column without detriment:

MATCH(Sheet2!$B2,Sheet1!$A:$A, 0)

Then Your index is the column that gets returned:

=INDEX(Sheet1!$C:$C,...)

So together

=INDEX(Sheet1!$C:$C,MATCH(Sheet2!$B2,Sheet1!$A:$A, 0))

One note if the name is not found it will return an error, you can capture that error with IFERROR and return something else:

=IFERROR(INDEX(Sheet1!$C:$C,MATCH(Sheet2!$B2,Sheet1!$A:$A, 0)),"Name not Found")

Now if the name on sheet2 is not in sheet1 then it will return Name not Found instead of an error.

Answer 3

I think you have just confused you matches.

=Index(sheet1!C:C,match(sheet2!B:B,Sheet1!A:A,0))