Go Back

How to Apply a VLOOKUP Between Worksheets

Figure 1. Sample Data set for VLOOKUP between Worksheets

VLOOKUP is one of the most used formulas in Excel. In its generic form VLOOKUP can only refer data from the same worksheet. To apply a VLOOKUP between worksheets, we must reference the other worksheet(s) in the VLOOKUP formula. In this tutorial, we will learn how to apply a VLOOKUP between worksheets.

The Generic formula for VLOOKUP

=VLOOKUP(lookup_value,range,column_number,match_type)

This formula allows VLOOKUP to lookup data from the same sheet. The following example uses a beverage sales dataset. It has the columns for the beverage name and prices in two worksheets.

To retrieve the sales for Margarita Sunshine in quarter 1, we use the formula =VLOOKUP(E4,A2:B9,2,FALSE) in cell E6. This will return the sales for Margarita Sunshine in the first quarter.

Figure 2. VLOOKUP Within Same worksheet

However, we would not be able to return the sales for Margarita Sunshine in quarter 3 using this formula. To do that, we would need to change the formula.

The Generic formula for VLOOKUP Between Worksheets

=VLOOKUP(lookup_value, sheet!range,column_number,match_type)

The only difference in this formula is that it includes the sheet name of the worksheet. This lets us apply VLOOKUP between two sheets. The reference of the second sheet as the lookup_value lets us look for the value in the other sheet. When VLOOKUP finds the value, the corresponding value is returned. The match_type is generally assigned as false to find an exact match.   

Example

To return the sales for Margarita Sunshine in quarter 3 from the previous example, we need to:

  • Go to cell E6.
  • Apply the formula =VLOOKUP(E4,'Qtr 3'!A2:B9,2,FALSE) to cell E6.
  • Press Enter.

Figure 3. How to Apply VLOOKUP between Worksheets

This will help apply VLOOKUP between the two worksheets. It will return the  sales for Margarita Sunshine in the third quarter.

Notes

We need to ensure the lookup_value exists in the second sheet. Otherwise, Excel will generate an #N/A error.

Most of the time, the problem we will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
How do you apply vlookup to multiple cells
An Excelchat Expert solved this problem in 29 mins!
Need help editing a formula for a vlookup comparing two worksheets
An Excelchat Expert solved this problem in 26 mins!
I am trying to perform a vlookup between two different worksheets to link customer id with customer name. However, the formula only works for the first few sets and not all the way through the worksheet.
An Excelchat Expert solved this problem in 24 mins!
Here are some problems that our users have asked and received explanations on
vlookup across worksheets problem
Solved by E. D. in 27 mins
I am trying to create a summary list page that pulls the same information about a recipe from multiple worksheets. Each recipe has a worksheet and then I"m using VLookup to locate based on recipe code. I can get it when only referencing one sheet. I want to be able to search multiple worksheets for the recipe code and bring back the correct data. I can't name a range because new recipe worksheets will continually be added and the naming isn't consistent. I thought I could search all worksheets between two worksheets and then just have the user always add the new recipe worksheets between the start and end. Possible?
Solved by X. A. in 30 mins
/how apply excel in vlookup and hlookup
Solved by D. U. in 18 mins

Leave a Comment

avatar