VLOOKUP Example Between Two Sheets in Excel

In Microsoft Excel, the VLOOKUP function is generally used to look up a value in the leftmost column of a table and then return a value in the same row from a specified column. In this article, you’ll find how you can use this VLOOKUP function between two sheets in Excel and extract data from another worksheet with proper examples and illustrations.

VLOOKUP Between Two Sheets in Excel: 4 Examples

Example 1: Use of VLOOKUP Between Two Sheets in the Same Excel Workbook

In the following picture, Sheet1 is representing some specifications of a number of smartphone models.

And here is Sheet2 where only two columns from the first sheet have been extracted. In the Price column, we’ll apply the VLOOKUP function to get the prices of all devices from Sheet1.

The required formula in the first output Cell C5 in Sheet2 will be:

=VLOOKUP($B5,Sheet1!$B$5:$E$14,4,FALSE)

After pressing Enter, you’ll get the price of the first smartphone device extracted from Sheet1.

Now you have to use Fill Handle to autofill the rest of the cells in Column C. And the final outlook should be as follows:

Example 2: Use of VLOOKUP Between Two Sheets in Different Workbooks

Now we’ll use the VLOOKUP function to extract data from another worksheet in another workbook.

For example, the following primary data table is lying in a workbook named Book1.

And here is another workbook named Book2 that will represent the output data extracted from the first workbook.

In the second workbook, the required formula in the first output Cell C5 will be now:

=VLOOKUP(B5, [Book1.xlsx]Sheet1!$B$5:$E$14,4, FALSE)

After pressing Enter and auto-filling the rest of the cells in the Price column, you’ll get all the output data right away as shown in the picture below.

Note: While extracting data from a different workbook, you must remember that both workbooks have to be kept open. Otherwise, the mentioned formula won’t work out and will return a #N/A error.

Example 3: IFERROR with VLOOKUP across Two Worksheets in Excel

Sometimes the lookup value may not be found in the primary data table. In that case, we can use the IFERROR function to customize an error message and show the output when the formula returns.

For example, in the following picture, the smartphone device in Cell B5 is not available in Sheet1. So, in the output Cell C5, the VLOOKUP function should return an error value. But we’ll now replace the error value with a customized message “Not Found”.

So, the required formula in Cell C5 should be now:

=IFERROR(VLOOKUP(B5,Sheet1!B5:E14,4,FALSE),"Not Found")

After pressing Enter and auto-filling the entire column, we’ll get the following outputs.

Example 4: Combining INDIRECT with VLOOKUP for Two Sheets in Excel

The INDIRECT function returns a reference specified by a text string. By using this INDIRECT function inside, the VLOOKUP function will pull out data from a named range in any worksheet available in a workbook.

At first, we have to define a name for the selected range of cells B5:E14 in the Name Box. Let’s assume, we’re naming it ‘Specs’ as the data table is representing the specifications of the smartphone devices in a nutshell.

Now, in Sheet2, the required formula in the output Cell C5 will be:

=IFERROR(VLOOKUP(B5,INDIRECT("Specs"),4,FALSE),"Not Found")

After inputting the mentioned formula and dragging it down to the last cell, you’ll get similar results as found in the other three previous examples in this article.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

VLOOKUP Example Between Two Sheets.xlsx

Concluding Words

So, these are all four quick and simple formulas with the VLOOKUP function to draw out data from another worksheet. If you have any questions or feedback, please let me know in the comment section.