Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Forecasting and Modeling
      • Alteryx
        • Introduction to Alteryx
      • Power BI
        • Power BI – Beyond the Basics
        • Power BI
      • Qlik Sense
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop
      • Python
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Sign Up
Home > Microsoft Excel > Battle of the Excel Lookup Functions: VLOOKUP vs INDEX/MATCH vs XLOOKUP

Battle of the Excel Lookup Functions: VLOOKUP vs INDEX/MATCH vs XLOOKUP

(Note: This article on Excel Lookup functions is suitable for Excel 2019, 2021 and Microsoft 365 users)

In this article, let us explore the three popular methods of performing lookups in Excel and see how they fare against each other.

You’ll learn:

  • Excel Lookup Functions – An Overview
  • VLOOKUP
  • INDEX MATCH
  • XLOOKUP

Excel Lookup Functions – An Overview

Using a formula to look up information in a dataset is one of the most popular ways to analyze information in Excel. Excel lookup functions will search for a lookup value that is common between two tables and return a corresponding value from another column. It is very similar to how a phonebook works: You use it to look up a contact’s name and find the contact’s matching phone number from the data.

In the following example, we need to complete the table on the left and fill the empty columns, ‘Category’ and ‘Price’. We have the information we need in the table on the right, referred to as the lookup table.

The “Product” column is common to both the tables and hence is the lookup value. We use it to look up and return the corresponding ‘Category’ and ‘Price’ data from the main table.

Battle of the Excel Lookup Functions
Excel Lookup Functions – An Overview

We can perform these lookups using a few different formulas in Excel: VLOOKUP, INDEX/MATCH or XLOOKUP, but what is the difference between them? What are the advantages and disadvantages of each of these Excel lookup functions? Which one will work best for you?

In this ‘Battle of the Lookups’, we will explore all of them and it is up to you to decide on your ultimate champion!

Related:

COMPARE TWO LISTS USING VLOOKUP

VLOOKUP FOR DUMMIES (OR NEWBIES)

XLOOKUP GOOGLE SHEETS – 4 BEST ALTERNATIVES!

VLOOKUP

VLOOKUP is the most well known and popular of all the Excel Lookup functions. It’s flexible and easy to use, but it does have its own share of drawbacks. 

Let’s use VLOOKUP to complete the ‘Category’ and ‘Price’ information for each product, in the following example.  

  • Click in cell F4. 
  • Type =VLOOKUP(
  • Note the syntax and arguments. 

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

lookup_valueThe value that is common between the tables
table_arrayThe table that contains the data (lookup table)
col_index_numThe column that contains the value to return
range_lookupTRUE or FALSE (Exact or Approx.) match of the lookup value in the table (optional)

In this example, the lookup_value is ‘TV’, the table_array is the lookup table range (F4 to make the cells absolute), the col_index_num is ‘2’ because we want to return the ‘Category’ and the range_lookup is ‘FALSE’ because we want to exactly match the word ‘TV’ in the table. 

NOTE: The range_lookup argument is optional and has two possible inputs: TRUE or FALSE. TRUE means we are doing an approx. match of the lookup value in the table and FALSE means we are doing an exact match. If this argument is left blank, the default is TRUE.

Use VLOOKUP to lookup values from top to bottom, and return values from the columns to the right of the look-up value
Use VLOOKUP to lookup values from top to bottom, and return values from the columns to the right of the look-up value
  • Double-click on the fill handle to copy the formula down. 
Excel VLOOKUP function
Excel VLOOKUP function

Next, we need to complete the ‘Price’ column. The VLOOKUP formula is the same except this time the col_index_num is ‘3’ as we want to return the information in the third column. The range_lookup argument can be specified as ‘0’ for FALSE and ‘1’ for TRUE.  

VLOOKUP Syntax
VLOOKUP Syntax
  • Double-click on the fill handle to copy the formula down. 

Drawbacks of VLOOKUP

The major drawback of VLOOKUP is its ability to only look from left to right in a table. The lookup value must always be to the left of the value to return or VLOOKUP will not work. When we construct a VLOOKUP, we specify a col_index_num which must always be positive. 

For example, we could not use the ‘Category’ as the lookup_value and return the ‘Product’ because ‘Category’ is to the right of ‘Product’ in the table and we must specify a positive column number. 

To perform a more flexible lookup, we would need to use an alternative method. 

Suggested:

The Excel SMALL Function – 3 Best Examples

How to Make An Excel Forecast Sheet in 3 Steps – The Best Guide

How to Use Excel COUNTIFS: The Best Guide

INDEX MATCH

INDEX MATCH is technically not one of the Excel lookup functions, since INDEX and MATCH are two separate functions in their own right. But they are commonly used together to create powerful, flexible lookups when VLOOKUP isn’t quite cutting it.

The INDEX/MATCH combo can return a value from a table regardless of where the lookup value is. Let us see how to use this combo in the same example.

  • Start by typing, =INDEX(

The first argument is an array. The array is the cell range that contains the value we want to return. In this example, it is the ‘Category’ data range: J3:J14

Use the INDEX MATCH combo to search for look-up values in the top-down direction, but return values from columns to both the left & right of the look-up value
Use the INDEX MATCH combo to search for look-up values in the top-down direction, but return values from columns to both the left & right of the look-up value

The next argument is row_num. We need to specify the row number that contains the value to return. In a small dataset, we could count down the items in the table but this is not efficient for large datasets. Instead, we use the MATCH function to automate the finding of the row number. We can do this by using the formula : MATCH(E4,$I$3:$I$14)

Here, the cell E4 is the lookup_value, i.e the value to lookup in the table

I3:I14 is lookup_array, i.e the cell range that contains the lookup_value

[match_type] is optional, specifies if Exact or Approx. match of the lookup_value in the table. Set it to 0, for exact match.

Use MATCH inside INDEX to search for the row which contains the look-up value
Use MATCH inside INDEX to search for the row which contains the look-up value
  • Drag the fill handle to copy the formula down. 

So, INDEX and MATCH are more flexible than VLOOKUP, but it’s a more complex formula to remember. 

What if we have duplicate lookup values? Both VLOOKUP and INDEX/MATCH will return the first occurrence in the list of the lookup value. If we want the second occurrence, we need to think of an alternative lookup method. 

XLOOKUP

XLOOKUP is the latest entry into the Excel lookup functions family. If you have ever used (and struggled with) INDEX and MATCH or VLOOKUP, to perform complex lookups, then XLOOKUP is for you. It is an easy to use, better alternative to other Excel lookup functions.

Like INDEX and MATCH, XLOOKUP can return values from any position in the table in relation to the lookup value but it can also search from the top or bottom which is useful if there are duplicate lookup values in the list. 

  • Type =XLOOKUP(
  • Note the syntax and arguments. 

=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]

lookup_valueThe value that is common between the tables
lookup_arrayThe range that contains the lookup value
return_arrayThe range that contains the value to return
if_not_foundText/value to display if the lookup_value is not found in the table
match_modeExact match, approx. match or wildcard match of the lookup_value
search_modeSearch from first to last, last to first or binary search
XLOOKUP Syntax

In this example, we have ‘TV’ listed twice in the lookup table. VLOOKUP and INDEX/MATCH search from top to bottom and so will only ever return the first occurrence of ‘TV’ in the table. XLOOKUP can search from bottom to top. Also note, the product ‘Lamp’ has been removed from the lookup table. We can add text to display if the value is not found. 

Use XLOOKUP to look up values from in any direction and return values from columns in any direction
Use XLOOKUP to look up values from in any direction and return values from columns in any direction
  • Type =XLOOKUP(E4,$I$3:$I$13,$J$3:$J$13,”No Record”,0,-1)

The formula will look for the lookup_value (E4) in the lookup_array (I3:I13) and return the corresponding value in the return_array (J3:J13). If the value is not found in the table it will display the text ‘No Record’ in the cell. We are doing an exact match of the lookup value (0) and we are searching through the table from last to first (-1). 

XLOOKUP returns a custom value if_not_found
XLOOKUP returns a custom value if_not_found

Suggested Reads:

How to Shade Every Other Row in Excel? (5 Best Methods)

How to Superscript in Excel? (9 Best Methods)

How to Split Cells in Excel? 3 Easy Methods

Closing Thoughts

In this article, we have looked at what VLOOKUP, INDEX/MATCH and XLOOKUP, the three popular Excel lookup functions have to offer. So, how do they compare?

Lookup FunctionWhat’s good?What’s bad?
VLOOKUPEasy to remember. Suitable for most scenarios. Not very flexible. Can only lookup information to the right of the lookup value. 
INDEX/MATCHPowerful and flexible. Lookup information wherever it is in relation to the lookup value. Quite a complex formula. Hard to remember. Searches from top to bottom only. 
XLOOKUPEasy to remember. Powerful and flexible. Choose a search mode, match mode and text to display if the value isn’t found. Only available in the latest versions of Excel (2019, 2021 and 365). 
Excel Lookup Functions – Pros and Cons

It’s up to you to determine which lookup method you prefer. Personally, I am a huge fan of XLOOKUP as it combines the best features of both VLOOKUP and INDEX/MATCH plus adds some additional functionality. 

If you would like to read more high quality Excel-guides, please check out our free Excel resources section.

Want to learn more about Excel? Click here to access our advanced Excel courses with in-depth training modules. You can train your entire team in Excel and other business software, for a low one-time monthly fee here.

Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.

30 day vertical banner

Most Popular Posts

  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15
  • How to Create Charts and Graphs in Microsoft Excel 2016

Similar Posts

Introduction to Using Layers in Adobe Photoshop Elements 15 – Part 2

Navigating the Workspace in Microsoft Project 2016

How to Insert Comments in Excel 2013

How to Use Items in QuickBooks 2019 – Part 1 and 2

Microsoft Project 2019 – Entering Tasks

Breakout Rooms in Teams Tutorial

Course Categories

  • Web Development
  • QuickBooks
  • Microsoft
  • Adobe
  • Data Analysis

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebook
© 2022 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)