[excel] Excel - find cell with same value in another worksheet and enter the value to the left of it

I have a report that is generated in Excel which contains an employee's number, but not his/her name. Not every employee will be on this worksheet on any given day.

In a 2nd worksheet I have a list of all employees' numbers and names.

I want a formula in the first worksheet that looks for the same value (the employee number) on the 2nd workbook and then enters the value of the cell to the RIGHT of that (the employee's name) on the first workbook. Is there a way to do this? Thanks!

This question is related to excel excel-formula spreadsheet vlookup

The answer is


Assuming employee numbers are in the first column and their names are in the second:

=VLOOKUP(A1, Sheet2!A:B, 2,false)

The easiest way is probably with VLOOKUP(). This will require the 2nd worksheet to have the employee number column sorted though. In newer versions of Excel, apparently sorting is no longer required.

For example, if you had a "Sheet2" with two columns - A = the employee number, B = the employee's name, and your current worksheet had employee numbers in column D and you want to fill in column E, in cell E2, you would have:

=VLOOKUP($D2, Sheet2!$A$2:$B$65535, 2, FALSE)

Then simply fill this formula down the rest of column D.

Explanation:

  • The first argument $D2 specifies the value to search for.
  • The second argument Sheet2!$A$2:$B$65535 specifies the range of cells to search in. Excel will search for the value in the first column of this range (in this case Sheet2!A2:A65535). Note I am assuming you have a header cell in row 1.
  • The third argument 2 specifies a 1-based index of the column to return from within the searched range. The value of 2 will return the second column in the range Sheet2!$A$2:$B$65535, namely the value of the B column.
  • The fourth argument FALSE says to only return exact matches.

Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to excel-formula

Excel doesn't update value unless I hit Enter Referencing value in a closed Excel workbook using INDIRECT? Conditionally formatting cells if their value equals any value of another column Sum values from multiple rows using vlookup or index/match functions What does an exclamation mark before a cell reference mean? If two cells match, return value from third Excel - programm cells to change colour based on another cell Format numbers in thousands (K) in Excel Excel Formula which places date/time in cell when data is entered in another cell in the same row Excel formula to display ONLY month and year?

Examples related to spreadsheet

How can I quickly and easily convert spreadsheet data to JSON? EXCEL Multiple Ranges - need different answers for each range Excel - find cell with same value in another worksheet and enter the value to the left of it Importing Excel spreadsheet data into another Excel spreadsheet containing VBA Selecting non-blank cells in Excel with VBA

Examples related to vlookup

Check if an excel cell exists on another worksheet in a column - and return the contents of a different column "Unable to get the VLookup property of the WorksheetFunction Class" error Comparing two columns, and returning a specific adjacent cell in Excel VLook-Up Match first 3 characters of one column with another column Combining COUNT IF AND VLOOK UP EXCEL Excel- compare two cell from different sheet, if true copy value from other cell Vlookup referring to table data in a different sheet Excel - find cell with same value in another worksheet and enter the value to the left of it Remove #N/A in vlookup result Excel vba - convert string to number