Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Count unique text values with criteria
  • Get nth match with INDEX / MATCH in Excel
  • How to reference named range different sheet in Excel
  • How to use Excel INDIRECT Function
  • How to use Excel MATCH Function

Data Analysis Examples

  • Error Bars in Excel
  • Excel Pie Chart
  • Get column index in Excel Table
  • How To Create Frequency Distribution in Excel
  • Conditional Formatting New Rule with Formulas in Excel

Data Validation Examples

  • Excel Data validation require unique number
  • Excel Data validation allow weekday only
  • Excel Data validation date in next 30 days
  • Excel Data validation must contain specific text
  • Excel Data validation specific characters only

VLOOKUP from another sheet in Excel

by

This tutorial shows how to calculate VLOOKUP from another sheet in Excel using the example below;

Formula

=VLOOKUP(lookup,sheet!range,column,match)

Explanation

Using VLOOKUP from another sheet is very similar to using VLOOKUP on the same sheet.

In the example shown, the formula in F5 is:

=VLOOKUP(B5,Sheet2!$B$5:$C$104,2,0)

Here, VLOOKUP pulls the correct building for each employee on Sheet2, into the table on Sheet1.

Worked Example:   Basic Tax Rate calculation with VLOOKUP in Excel

How this formula works

In this example, we have a list of employee locations like this on Sheet2:

On Sheet1, we retrieve the building location for each team member using this formula:

=VLOOKUP(B5,Sheet2!$B$5:$C$104,2,0)

For lookup value, we simply use the value in column B, the employee id.

Worked Example:   VLOOKUP by date in Excel

For the table array, we we use the range B54:$C$104 qualified with a sheet name:

Sheet2!$B$5:$C$104 // includes sheet name

This is the only difference from a standard VLOOKUP formula — including the sheet name simply tells VLOOKUP which sheet to use for the table lookup range.

Finally, column number is 2, since the building names appear in the second column, and VLOOKUP is set to exact match mode by including zero as the forth argument. This ensures that we get the correct building for each team member and a #N/A error if for some reason the id is not found in the location table.

Worked Example:   VLOOKUP with two client rates in Excel

Post navigation

Previous Post:

Two-way lookup with INDEX and MATCH in Excel

Next Post:

VLOOKUP with 2 lookup tables in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • SEARCH, SEARCHB functions: Description, Usage, Syntax, Examples and Explanation
  • How to extract name from email address in Excel
  • DOLLAR function: Description, Usage, Syntax, Examples and Explanation
  • How to count line breaks in cell in Excel worksheet
  • How to get first word in Excel
  • Get first day of month in Excel
  • Add months to date in Excel
  • Display the current date and time in Excel
  • Next biweekly payday from date in Excel
  • Add workdays no weekends in Excel
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • Bond valuation example in Excel
  • How to set check register balance in Excel
  • AMORDEGRC function: Description, Usage, Syntax, Examples and Explanation
  • Calculate original loan amount in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles