How To Do A VLOOKUP Between Two Sheets

VLOOKUP (Vertical Lookup) is a useful Excel function that helps you find information in a table or range. If you have data spread across two different sheets, you can still use VLOOKUP to fetch values from one sheet to another. This is particularly helpful when working with large datasets, like inventory lists, employee records, or sales reports. Here is how to do a VLOOKUP between two sheets.

1. Understand the Purpose of VLOOKUP

VLOOKUP searches for a value in the first column of a range and returns a corresponding value in the same row from another column. When you’re working with two sheets, one sheet will contain the reference value (e.g., an ID or product code), and the other will hold the data you want to retrieve.

2. Prepare Your Data

Start by ensuring the data is clean and organized:

• In Sheet1, enter or identify the values you want to look up (e.g., employee IDs).
• In Sheet2, make sure the first column contains matching values (e.g., employee IDs) and the column that holds the data you want to retrieve (e.g., employee names) is clearly defined.

3. Write the VLOOKUP Formula

Go to Sheet1, where you want the result to appear. Click the cell where you want the VLOOKUP result and type the following formula:

=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)

Here’s what each part means:

• A2: the cell in Sheet1 containing the lookup value.
• Sheet2!A:B: the range in Sheet2 you want to search through. Column A has the lookup values, and column B has the return values.
• 2: tells Excel to return the value from the second column in the range.
• FALSE: ensures an exact match.

Press Enter and Excel will return the matching value from Sheet2.

4. Copy the Formula Down

To apply the formula to the rest of the column, drag the fill handle (a small square at the bottom-right corner of the cell) down the column. Excel will adjust the row numbers automatically.

5. Troubleshoot Common Issues

• If the formula returns #N/A, it means Excel can’t find a match. Check for typos or mismatched formats.
• Make sure both sheets have consistent data types (e.g., both lookup columns should be text or both should be numbers).
• Confirm that the lookup range includes the correct columns and that the lookup value is in the first column of that range.

Also Read: How To Disable Laptop Keyboard