EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Lookup & Reference Functions in Excel Excel VLOOKUP From Another Sheet
Secondary Sidebar
Excel Functions
  • Lookup and Reference Functions in Excel
    • VLOOKUP Function in EXCEL
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training

Excel VLOOKUP From Another Sheet

By Madhuri ThakurMadhuri Thakur

VLOOKUP From Another Sheet

Excel Vlookup from Another Sheet (Table of Contents)

  • Vlookup From Another Sheet in Excel
  • Example of Vlookup from Another Sheet in Excel

Vlookup from Another Sheet in Excel

VLOOKUP is an excel function used by excel users who usually need to work with more than one worksheet. It has the ability to extract your data from another worksheet in a very flexible and organized way. In simple terms, this function takes the user’s input, searches for it in the excel worksheet, and returns a matching value related to the same input. Speaking in a technical way, the generic definition of the VLOOKUP function is that it looks up for a value in the first column of the specified range and returns a similar value in the same row from another column. The VLOOKUP function is available in all versions of Excel, right from Excel 2007 to Excel 2016.

VLook up from another sheet Example 1-1

The VLOOKUP function consists of 4 parameters- lookup_value, table_array, col_index_num, and range_lookup. Let us see in detail what do these 4 parameters define in the above syntax.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

  • Lookup_value – Here, the user needs to define the value which needs to be searched. It can be either a value that consists of number, date, or text, or else it can be a cell reference.
  • Table_array – Here, the user will define the range of cells within which they need to search the data. Also, values searched are case sensitive, i.e. it can search for values in the form of text that can be upper or lower case and find them out.
  • Col_index – Here, the value entered refers to the column within the table array in which the users need to find the value.
  • Range_lookup – This parameter is optional but is an important parameter to consider. It returns an approximate match if the range lookup value is TRUE and returns exact matches if the range lookup is FALSE.

Example of Vlookup from Another Sheet in Excel

Let us consider an example as shown in the below examples, where we have taken 2 worksheets wherein sheet1 there are names of players with its jersey numbers, but with missing runs scored columns. This column needs to be extracted in sheet1 from sheet2.

You can download this Vlookup from Another Sheet Excel?Template here – Vlookup from Another Sheet Excel?Template

Vlookup from Another Sheet – Example # 1

Example 1-1

Vlookup from Another Sheet – Example # 2

Example 1-2

As we can see in example 2 above, additional columns are there, such as Columns C and D, when compared with sheet2. We need to retrieve data of Column C from sheet2 to sheet 1. The steps to perform the VLOOKUP function are as follows.

  1. First, enter the formula =VLOOKUP (A2, Sheet2!$A$2:$D$7,3, FALSE) in cell C2, and press enter.
  2. We can either type the formula in every cell, but copy-pasting will be easier and more efficient.

Let us see the steps below, which will explain the above steps and formula in detail.

Step – 1

VLook up from another sheet Example 2-Step 1

As we can see in above Step1, the formula entered in sheet1 in cell C2 is =VLOOKUP (A2, Sheet2!$A$2:$C$7,3, FALSE) where,

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,813 ratings)
  • A2 represents the lookup value which is the jersey number, and it’s the same value in both the sheets.
  • Sheet2! is the name of the sheet.
  • $A$2:$C$7 is the range of data in sheet2 where we are searching data for the jersey number so as to fetch the data for runs scored in sheet2. And also, the’$’ sign is used to fix the data range, so it makes it easier to copy and paste in other cells.
  • 3 is the column index-number which contains data in the 3rd column in sheet2 and will retrieve data from the range A2: C7.
  • FALSE will return the exact match if the lookup is in range.

Step – 2

Click on cell C2 and drag down the cell from the corner to apply the formula in all the below cells, as shown in the below image.

VLook up from another sheet Example 1-2

The above image is a snapshot of sheet1 where column C ‘Runs Scored’ is filled using the VLOOKUP formula.

VLOOK function can also be used in the same worksheet, as shown in the below image.

Step – 3

VLook up from another sheet Example 1-6

As we can see in Step 3, we have entered the formula in cell E2 to lookup value for in cell C4. Here in this formula, we have not mentioned the name of the sheet, as we are searching for value in the same sheet. A4 is the lookup value; A2: C7 is the search range, i.e. table_array, and 3 is returned matching value from column C.

Step – 4

We can see the result returned in the below image.

Example 1-7

Advantages of Vlookup from Another Sheet

  • This function helps the user search for data in the same sheet or even when there are multiple sheets present in the workbook.
  • VLOOKUP function is much useful than the MATCH function, as the MATCH function does not return the value of data; it only returns the position of that value.
  • VLOOKUP helps the user even to extract data from one worksheet to another worksheet.
  • Also, the most important feature offered by the VLOOKUP function is that values can be looked up from different workbooks as well.
  • The main advantage of the VLOOKUP function is when the user types text in order to be searched, it does not check whether the text is the lower or upper case as the function is not case sensitive.

Disadvantages of Vlookup from Another Sheet

  • It is difficult for a new user to understand how the formula is implemented and know about its parameters.
  • In case if the user is performing an approximate match, then the first column of table_array needs to be sorted in ascending order. If not, it will return the correct value. Hence, this function needs to be executed with proper knowledge of the formula.

Things to Remember

  • The VLOOKUP function never searches for the data on the left. As it always takes up value for the lookup value in the leftmost top column and starts the search from left to right.
  • Always remember, when we enter a value less than 1 in the col_index_num parameter, there will be an error returned by the formula.
  • For an efficient way, use the symbol ‘$’ (absolute cell references) for the table_array parameter so that the user can copy and paste the formula into other cells without any error.
  • Also, there is no need to type the entire formula, as when it comes to the part of entering the sheet name, you can switch the sheet and select the range using the mouse.

Recommended Articles

This has been a guide to Vlookup from Another Sheet in Excel. The easiest way to adjust a column is to use the Vlookup from Another Sheet in Excel. Here we discuss how to use Vlookup from Another Sheet in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. VLOOKUP Function in Excel
  2. VBA LOOKUP
  3. VLOOKUP Examples in Excel
  4. VLOOKUP Tutorial in Excel
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
5 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Vlookup from Another Sheet Excel?Template

EDUCBA

Download Vlookup from Another Sheet Excel?Template

EDUCBA

डाउनलोड Vlookup from Another Sheet Excel?Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more