Excel formula to copy text from another sheet when data in sheet 1 matches

microsoft excel

I have been trying to find a formula, or combination of formulas that will copy a value from sheet 2 when I enter a name in sheet 1.

This is for a timesheet that requires ID numbers. Sheet 1 is the timesheet that goes to payroll where column A is the name of the employee for a specific shift and column B is their ID number. Sheet 2 is a table that has all employee names in column A and their ID numbers in column B.

I would like to be able to enter an employee's name in column A of sheet 1 and a formula would look through column A of sheet 2 for a match, and then return their ID number from column B of sheet 2 into column B of sheet 1.

Best Answer

In sheet 1, put the following in column B

=VLOOKUP(Sheet1!A2,Sheet2!A:B,2)

The VLOOKUP formula basically says this:

VLOOKUP(X,Y,Z)

"In the field where this formula exists, return what is in the Z numbered column of the range Y, when the value of the first column in Y matches the value in field X."

The source ("Y" in the example, Sheet2 for you) must be sorted by field X (the "Name" field for you) and field X ("Name") must be the first field in Y (Sheet2). VLOOKUP also returns the closest match when the fields are not sorted correctly or it cannot find the value, so it is also a good idea to verify X ("Name") exists in the Y (Sheet2) set before blindly returning the results, such as:

=IF(COUNTIF(Sheet2!A:A,Sheet1!A2) > 0, VLOOKUP(Sheet1!A2,Sheet2!A:B,2),"N/A")

Now you are verifying the value exists before you run the VLOOKUP which will avoid false returns mucking with your Sheet1 data. (Which could cause the wrong person to get paid, and someone else not to be paid at all)

It will also return the first match it finds, so the name values need to be distinct. So an additional check worth doing might be:

=IF(COUNTIF(Sheet2!A:A,Sheet1!A2) > 0, 
    IF(IF(COUNTIF(Sheet2!A:A,Sheet1!A2) > 1,
          "MORE THAN ONE MATCH",
          VLOOKUP(Sheet1!A2,Sheet2!A:B,2)
          ),
    "NAME NOT FOUND")
Related Question