Imagine you have two separate Excel spreadsheets. One contains a list of employee ID numbers and their salaries. The other contains a list of employee ID numbers and their names. If your boss asks for a single report showing every employee’s name next to their salary, manually copying and pasting that data line by line would take days. Instead, you need to use the most famous formula in business: Microsoft Excel VLOOKUP.
VLOOKUP stands for “Vertical Lookup.” It acts like a digital bloodhound. You give it a piece of information you already have (like an Employee ID), and it searches vertically down a different spreadsheet to find the matching row and extract the missing data (like the Employee Name). In this guide, you will learn how to write this formula step by step.
Step 1: Understanding the VLOOKUP Formula
To use VLOOKUP successfully, you must understand its four required arguments. The formula always looks exactly like this:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- Lookup_value: What are you searching for? (e.g., the Employee ID in cell A2).
- Table_array: Where should Excel look for it? (e.g., the other spreadsheet).
- Col_index_num: Which column contains the answer you want? (e.g., Column 2 has the names).
- [range_lookup]: Do you want an exact match? (Always type FALSE).
Step 2: Writing the Formula
Let’s execute the formula to find the name for Employee ID 104.
- Click the empty cell where you want the Employee Name to appear.
- Type
=VLOOKUP( - Click the cell containing the Employee ID (e.g., A2). Your formula now looks like
=VLOOKUP(A2, - Now, switch to your other spreadsheet (the one with the names). Highlight the entire table of data, making sure the Employee ID column is on the far left. Press F4 to lock the range (adding dollar signs like
$A$2:$C$100). Your formula is now=VLOOKUP(A2, Sheet2!$A$2:$C$100, - Count the columns in that highlighted table. If the IDs are Column 1, and the Names are Column 2, type the number 2. Add a comma. Formula:
=VLOOKUP(A2, Sheet2!$A$2:$C$100, 2, - Finally, type
FALSE)and press Enter.
The formula instantly searches the second sheet, finds ID 104, grabs the name next to it, and pulls it back into your main sheet.
Step 3: Troubleshooting Common Errors
VLOOKUP is incredibly strict. If it returns an error, it is almost always due to one of three reasons:
- #N/A: This means Excel searched the entire table and could not find the ID. Double-check that the ID actually exists in the other sheet.
- The Left-Most Rule: VLOOKUP can only search from left to right. The column containing your Lookup_value (the IDs) must be the very first column in your highlighted Table_array. If the Names are in Column A and the IDs are in Column B, VLOOKUP will fail.
- Data Types: If an ID is formatted as a “Number” on Sheet 1, but formatted as “Text” on Sheet 2, Excel will not recognize them as a match.
By mastering the VLOOKUP formula, you gain the ability to instantly merge massive datasets, transforming yourself from an Excel novice into a true data professional.