How to perform VLOOKUP between two sheets in Excel

You can trust PC Guide: Our team of experts use a combination of independent consumer research, in-depth testing where appropriate - which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

If you want to know how to perform VLOOKUP between two sheets in Excel, you’ve landed on the right page.

VLOOKUP means Vertical Lookup, which is a search Excel function that looks up data in the leftmost column of a range and generates the matching data from a specified column to its right. The VLOOKUP function is utilized when there’s a need to look up specific data by using a unique identifier such as an ID or item number. This article will guide you on how to perform a VLOOKUP between 2 sheets in Excel with visual aids.

So, without wasting another second, let’s dive in!

VLOOKUP example between two sheets

Here’s a table of data with Employee Demographic data in Sheet 1. For this example, let’s say we want to use Sheet 1 to look up Employee States and list them in Sheet 2.

Sheet 1

An Excel spreadsheet containing a list of employee names distributed across two sheets, with VLOOKUP functionality.

Sheet 2

An example of a VLOOKUP function used in an employee ID and state table across two sheets in Excel.

Now, let’s break the function down.

The VLOOKUP function’s syntax is as follows:

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

Pay close attention to the equal sign, parentheses, commas, and bracket locations.

How to perform VLOOKUP between two sheets

Enter the VLOOKUP function in cell B2 of Sheet 2 as shown in the visual below.

The VLOOKUP function includes the lookup_value, which is the value in which to lookup, in this case, the lookup_value is cell A2 of Sheet 2, the Employee ID.
Then the table_array is entered, which is the array of data in which the VLOOKUP should search. In this case, the table_array is Sheet1!A:D (Sheet 1, Columns A-D).
Then the col_index_num is entered, which is the column number of the data you wish to return. In this case, the col_index_num is 4 because the States we want to generate are listed in Column 4 of Sheet 1.
Then the range_lookup is entered, which is either TRUE (approximate match) or FALSE (exact match). In this case, we would use FALSE as we want to generate an exact match for Employee States.
Next, press Enter for the result.
The first result should be “New York” in cell B2 as shown below.

An example of a spreadsheet with VLOOKUP.

Then highlight the corner of cell B2 and drag the plus sign down to fill the formula in the remaining cells as shown below.

This example demonstrates how to use VLOOKUP to find the New York state employee ID in an Excel spreadsheet.

An employee id is shown as an example in an excel table, using VLOOKUP function to retrieve information from two sheets.

What to do if VLOOKUP returns #N/A error?

Now let’s explore #N/A error results for VLOOKUPs.

If an #N/A error (also known as not applicable) is displayed, it means there is no data available that corresponds with the selected unique identifier. In this case, the unique identifier is employee ID. For this example, let’s delete a corresponding Employee ID in cell A2, of Sheet 2, as shown below.

Directly after the deletion of the Employee ID in cell A2 of Sheet 2, the results from the previously entered VLOOKUP, turn to #N/A.

An employee id example is shown in an Excel table.

To replace the #N/A with specific text/integers, the IFERROR function can be utilized.
The IFERROR function returns a specified value if a result results in an error.
In this case, we want to replace the #N/As with the text “No Match”.
Place the IFERROR function before the VLOOKUP function in cell B2, then place the preferred text/integer at the end of the formula as shown below.

An example spreadsheet with two sheets, showcasing the number of employees in a state using VLOOKUP.

Then drag the new formula down to replace the previous formula as shown below.

An employee id is shown in an excel spreadsheet as an example.

Conclusion

And there you have it. This was how you can perform VLOOKUP between two sheets in Excel. As you can see for yourself, the process isn’t complicated and will hardly take you a few minutes. Learning about this function is pretty important, as it can help you out in many ways, such as finding data quickly in large sheets.

Learn more about VLOOKUP in Excel with these helpful guides: