Excel Lookup Formula to Create Combined Tables
Lookup functions (VLOOKUP and HLOOKUP) are among the most useful Excel functions to pull data from multiple column-/row-sorted lists into a unified table. Think of it this way, these functions ask a simple question and return the answer. Question: is the data from a specific cell reference in this column or row? Check out our example below that uses VLOOKUP to combine data from two tables.
Lookup functions
What it means: VLOOKUP (Vertical Lookup) asks if the data from a cell reference is in a specific column and HLOOKUP (Horizontal Lookup) asks if the cell reference data is in a specific row.
What you might use if for: Let’s use VLOOKUP to pull information from 2 lists, HR and Payroll, to create a Combined List with the headings: “Employee ID”, “Employee First Name”, “Employee Last Name”, “Dept.”
1. We’ll start by copying and pasting the HR List into the Combined List and entering “Dept” into the next column heading. Now we’ll fill in the Employee’s Dept using VLOOKUP.
The VLOOKUP function needs:
- a value to match
- a location to search
- the number of columns over to the data we want
- whether an exact match is needed or not
2. Locate the Employee ID 18648 in the Combined List.
3. Next, we’ll locate the “Employee ID” and “Dept” headings in the Payroll List cells (G2:H6) and count the columns from “Employee ID” to “Dept” to get the column_index 2.
4. Scan that many places along 18648’s row to select “Marketing.”
5. To enter “Marketing” as 18648’s Dept we’ll set that cell value equal to the result of this VLOOKUP formula:
“=VLOOKUP(18648,G2:H6,2,range_lookup=0)”
NOTE: The range_lookup must equal 0 for an exact match to 18648, or it should equal 1 for the closest possible match. (See the Excel documentation for a detailed explanation.)
6. Now, copy the Dept column formula down through all the table rows, and you’re done!
This may seem like a lot of steps to merge just two small files, but when you have multiple files and thousands of values, the initial effort to automate the process will be well worth your time and labor!