How to Use the Excel VLOOKUP Function to Find Data Across Worksheets

If you have a list of Employee IDs in one worksheet, and a massive database containing employee names, salaries, and departments in another worksheet, manually copying and pasting the names to match the IDs is inefficient and prone to errors. Microsoft Excel solves this with the VLOOKUP (Vertical Lookup) function. It searches for a specific value in one column and returns a corresponding piece of data from the same row.

Understanding the Syntax

The VLOOKUP formula requires four specific pieces of information to work correctly:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: What are you searching for? (e.g., Employee ID 1045).
• table_array: Where should Excel look for it? (e.g., the master database sheet).
• col_index_num: Once Excel finds the ID, which column contains the answer you want? (e.g., column 2 contains the Name).
• [range_lookup]: Do you want an exact match or an approximate match? (Always use FALSE for an exact match when dealing with IDs, names, or part numbers).

Step 1: Prepare Your Data

VLOOKUP has one absolute, unbreakable rule: The value you are searching for must be in the very first (leftmost) column of your table_array.

If your master database has Employee Names in Column A and Employee IDs in Column B, VLOOKUP will fail to find the ID. You must rearrange your master database so the Employee ID is in Column A.

Step 2: Write the VLOOKUP Formula

Imagine you are on “Sheet1” and you have Employee ID 1045 in cell A2. You want to pull their Name from “Sheet2”.

1. On Sheet1, click on cell B2 (where you want the name to appear).
2. Type =VLOOKUP(
3. lookup_value: Click on cell A2. The formula becomes =VLOOKUP(A2,
4. table_array: Now, click on the tab for “Sheet2” at the bottom of the screen. Highlight the entire data table (e.g., from A1 to D500). The formula becomes =VLOOKUP(A2, Sheet2!A1:D500,
5. col_index_num: Count the columns in your highlighted table on Sheet2. If ID is column 1, Name is column 2, and Department is column 3, and you want the Name, type 2. The formula becomes =VLOOKUP(A2, Sheet2!A1:D500, 2,
6. [range_lookup]: Type FALSE to force an exact match.
7. Close the parenthesis and hit Enter. The final formula looks like this:
   =VLOOKUP(A2, Sheet2!A1:D500, 2, FALSE)

Step 3: Lock Your Table Array (Absolute References)

If you drag the formula down to apply it to 100 other Employee IDs, Excel will automatically adjust the row numbers. A2 will correctly become A3, but your table array Sheet2!A1:D500 will incorrectly shift down to Sheet2!A2:D501, causing you to miss data at the top of the list.

You must “lock” the table array using dollar signs (Absolute References).

Change your formula to:
=VLOOKUP(A2, Sheet2!$A$1:$D$500, 2, FALSE)

Now, when you drag the formula down column B, the lookup value changes, but the search area remains perfectly locked in place.

Troubleshooting #N/A Errors

If your VLOOKUP returns an #N/A error, it means Excel could not find an exact match. This usually happens for three reasons:

1. The ID simply doesn’t exist in the master database.
2. There are hidden spaces in your data (e.g., “1045 ” instead of “1045”). Use the TRIM() function to clean your data.
3. Formatting mismatches. If the ID is formatted as a Number on Sheet1, but formatted as Text on Sheet2, VLOOKUP will not recognize them as a match. Ensure both columns use the exact same data format.