Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Dec 2 '12 at 18:52

This question came from our site for professional and enthusiast programmers.

2  
That is Vlookup ! –  iDevlop Nov 30 '12 at 17:29
    
Thank you, someone told me to try doing index and match in combination but i go this to work perfectly –  user1867031 Nov 30 '12 at 17:48

2 Answers 2

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")
share|improve this answer
    
Everything about your suggestion works perfectly, I just wanted to offer a couple of improvements. "VLOOKUP also returns the closest match when the fields" - unless you add ",FALSE" to the end of the function. Furthermore, using IFERROR (Excel 2007+, there are ways round it in 2003 with =IF(ISERROR... ) allows you to mask the errors. –  Gary Dec 4 '12 at 0:38

=SUMIF(sheet2!A8,"="&A8,Sheet2!G8)

Where you will be @ Sheet 1

Click on Column B any row on Sheet 1 and enter the formula above.

If cell ((A8 (Name)) on sheet 2 equals cell ((A8 (Name)) on current Sheet that I assume is sheet 1 then give the cell Number from sheet 2 were the ID Number is allocated.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.