If you have two massive spreadsheets—one containing a list of employee ID numbers and names, and another containing ID numbers and salaries—manually matching the data between the two sheets takes hours and invites disastrous human error. To automate this cross-referencing process, you must master the most famous function in Microsoft Excel: VLOOKUP (Vertical Lookup).
VLOOKUP tells Excel to search vertically down a specific column for a specific value (like an Employee ID). When it finds that ID, it looks across the row to grab related information (like the Salary) and pulls it back into your current spreadsheet. In this guide, you will learn the exact syntax to build a flawless VLOOKUP formula.
The VLOOKUP Syntax Explained
Every VLOOKUP formula requires exactly four pieces of information, separated by commas. The fundamental structure is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Let’s break down what each of these intimidating terms actually means in plain English.
1. lookup_value (What are you looking for?)
This is the piece of data you already know. If you are trying to find the salary for Employee ID “105”, the ID “105” is your lookup value. Usually, you just click on the cell containing this value (e.g., A2).
2. table_array (Where should Excel look?)
This is the massive block of data where the answer is hiding. You highlight the columns on the other spreadsheet that contain the data. Crucial Rule: The column containing your lookup value (the Employee ID) must be the very first (leftmost) column in the range you highlight. (e.g., Sheet2!A:C).
3. col_index_num (Which column contains the answer?)
Once Excel finds Employee ID 105 in that other spreadsheet, how many columns across should it count to find the Salary? You must enter a number here. If the ID is in column 1, Name is in column 2, and Salary is in column 3, you would enter the number 3.
4. [range_lookup] (Do you want an exact match?)
This is the most common point of failure. You must tell Excel if it should guess or be precise.
- Type FALSE if you want an exact match. (If it cannot find ID 105, it returns an error).
- Type TRUE if you want an approximate match. (This is almost never recommended for database work, as it will grab the closest ID it can find, giving you the wrong person’s salary).
Always use FALSE unless you are calculating tax brackets or grading tiers.
Putting it All Together
If you are in cell B2 and want to pull the salary for the Employee ID listed in cell A2, your final formula will look like this:
=VLOOKUP(A2, Sheet2!A:C, 3, FALSE)- Type the formula into the cell and press Enter. The correct salary will instantly appear.
- To apply this to the rest of your employees, simply click the small green square in the bottom right corner of the cell and drag it down the column. Excel will automatically run the VLOOKUP for every single person on your list.
Troubleshooting the #N/A Error
If Excel returns an `#N/A` error, it means the formula worked, but the exact lookup value simply does not exist in the other spreadsheet. The most common causes are:
- Typographical errors in the ID number.
- Hidden spaces at the end of the ID number (e.g., “105 ” instead of “105”).
- The lookup column in the source data was not the very first column on the left.
By mastering the VLOOKUP function, you transform Microsoft Excel from a simple calculator into a powerful relational database, capable of merging massive datasets in seconds.