VLOOKUP Example Between Two Sheets in Excel

Example 1 – Using 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)

Press Enter and you’ll get the price of the first smartphone device extracted from Sheet1.
Use Fill Handle to autofill the rest of the cells in Column C.

Example 2 – Using VLOOKUP Between Two Sheets in Different Workbooks

The following primary data table is in a workbook named Book1.

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:

=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, both workbooks have to be open. Otherwise, the formula won’t work and will return a #N/A error.

Example 3 – IFERROR with VLOOKUP Across Two Worksheets in Excel

In the example, 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. We’ll now replace the error value with a customized Not Found.