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 > XLOOKUP vs VLOOKUP – 5 Key Differences

XLOOKUP vs VLOOKUP – 5 Key Differences

The Lookup functions are one of the most very popular formulas in Excel. They are used to search for one value in a column and return the corresponding value from another column. Over the years, the VLOOKUP function has become one of the most frequently used and must know lookup formulas. But, Microsoft Excel had recently introduced a new lookup function called XLOOKUP for its latest Office 2019 and Office 365 versions. In this article, we’ll compare XLOOKUP vs VLOOKUP and see which one is the better option for searching values in Excel.

Table Of Contents
  1. The XLOOKUP Function 
    • Syntax
    • Parameters
    • XLOOKUP Example
  2. The VLOOKUP Function
    • Syntax
    • Parameters
    • VLOOKUP Example
  3. XLOOKUP vs VLOOKUP – Key Differences
  4. Closing Thoughts

The XLOOKUP Function 

The XLOOKUP function is an improved version of various Excel LOOKUP functions. Use it to search a specific range or an array of a specified value in a worksheet, both vertically and horizontally. And, it returns the corresponding value in another column.

Knowing exactly how it works can be helpful. Here is the syntax with arguments: 

Syntax

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

Parameters

  • Lookup_value – Specifies the value to search for.
  • Lookup_array – It denotes the cell range where the function has to search.
  • Return_array – Specifies the range or array to extract the values.
  • [if_not_found] – This is optional, and it is the value or message to return if there’s no match. If it is not mentioned, the formula returns a #N/A error.
  • [match_code] – This is optional, and you can specify 0, -1, 1, or 2 depending on the match type you want to perform.

 0 –  It returns an exact match is found or else it returns #N/A.

-1 –  It returns an exact match if found. Else returns the next smaller data.

 1 –  It returns an exact match when found. Else returns the next larger data.

 2 –  It returns a wildcard match where *,? and ~ have a special meaning.

  • [search_mode] – This too is optional, and you specify 1, -1, 2, or -2 depending on the direction of the search.

 1 – It starts its searches from the first data. This is the default option.

-1 – It searches from the last data, in the reverse order.

 2 – It performs a binary search and requires that the lookup_array be sorted in ascending order. The function will return an invalid result if the lookup_array is not sorted.

-2 -It performs a binary search and requires that the lookup_array be sorted in descending order. The function will return an invalid result if the lookup_array is not sorted.

XLOOKUP Example

In the example below, the query is to return the First_Name of the employee who works as an HR. The lookup_value is in cell B9 as HR. The formula to find that is entered in cell C9 as =XLOOKUP(B9, D2:D6, B2:B6,,0,1).

You can notice that XLOOKUP is performing a backward search. The lookup_value is in column D and the result has to be searched in column B. This kind of backward search is not possible in any other lookup function.

XLOOKUP in Excel
XLOOKUP in Excel

In the optional fields, the first field, [if_not_found] is left empty. The second optional field, [match_code] is mentioned as 0 to return the exact value. The third field, [search_mode], is mentioned as 1 to begin the search from the first field. That is the reason the formula returns the answer as Edith. 

The query is to find the First_Name of the employee who works in the HR department. You can find that there are 2 employees, Edith and Robert, in the HR department.

To return the other employee’s name, Robert, use the same formula and just mention the third parameter as -1. This will begin its search in the reverse order from the bottom. The name Robert is seen first and hence it returns the same.

The VLOOKUP Function

In simple words, the VLOOKUP function works like an index page in a book where we look at the topic to locate its page number. 

The VLOOKUP function searches for the value across columns. Basically, it searches vertically and hence the name VLOOKUP where ‘V’ stands for vertical.

The function takes a lookup value, table reference, and column index as mandatory parameters. The last parameter is optional, which specifies the return value. It could either return an exact match or an appropriate match. 

It is mandatory that the VLOOKUP function has a known value as an argument.  Now, let’s look at the syntax and understand it with an example.

Syntax

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

Parameters

  • Lookup_value – This is the value to search across the cells, get the match, and return the corresponding value from the other column.
  • Table_array – This argument takes the starting value and the ending value of the cells the function has to search.
  • Col_index_num – The value in this argument represents the index of the column to look into.
  • range _lookup – It accepts 2 values, one is TRUE and another is FALSE. If TRUE is mentioned in the formula, then the function returns the closest match. If FALSE is mentioned, the function returns the exact match.

VLOOKUP Example

VLOOKUP in Excel
VLOOKUP in Excel

In the example above, we have an employee table that contains the details including Emp_ID, First_Name, Last_Name, Dept, and Salary.

The data is spread across the cells from A2 to E6. This cell range is referred to as table_range. The query here is to find the employee’s salary if the Emp_ID is given.

Imagine the same situation in a real-world environment where a manager seeks an answer from a table that has thousands of rows. This is where VLOOKUP is used to automate the search and obtain the exact result.

The formula used to find the salary of an employee whose Emp_ID is 27 is VLOOKUP(C8, A2:E6, 5, FALSE).

Cell C8 contains the lookup_value 27. The formula searches for this value in the table spread across the range A2 to E6. The column number is 5 because it is in the 5th column from where the lookup_value is referenced.

The last parameter is an optional parameter and we mention it as FALSE so it returns an exact match. If it is mentioned as TRUE, the function returns an appropriate match if an exact match is not found.

The formula works like this:

VLOOKUP(C8,..,..,..) – takes the value at C8, which is 27.

VLOOKUP(C8, A2:E6,..,..) – searches for 27 in the cells from A2 to E6. It finds it in column A, 5th row. 

VLOOKUP(C8, A2:E6, 5,..) – From column A and the 5th row, it moves 5 columns left as mentioned in the formula. Here, 5 is the column index.

VLOOKUP(B9, B2:F6, 5, FALSE) – The exact value is available in the 5th column which is the salary column. It returns the value of 25,000 which is the salary of the employees whose ID is 27.

XLOOKUP vs VLOOKUP – Key Differences

XLOOKUPVLOOKUP
1XLOOKUP searches for data both horizontally and vertically.VLOOKUP searches only vertically.
2XLOOKUP can refer to the left of the lookup_value.It always looks up the values towards the right of the lookup_value.
3It has optional fields, the match_mode, and the search_mode.VLOOKUP does not have optional fields.

4
In case a match cannot be found in the lookup_table, XLOOKUP supports an optional parameter called not_found which overrides the #N/A error.VLOOKUP returns the default error message #N/A error.
5In XLOOKUP, you need to specify the lookup_value column and the return_value column alone.In VLOOKUP, you need to specify the entire table_array.

Closing Thoughts

In this article, we learned how the XLOOKUP and the VLOOKUP function work with the help of a few examples. The XLOOKUP function, unlike other lookup functions, can work vertically and horizontally with the same syntax. You only need to ensure that you provide values for the lookup and the return array to get the result you desire. 

Use VLOOKUP to search across a huge database when you are sure that it is a fixed table and the items to be searched are on the right.

Please note that the XLOOKUP function is only available in Excel for Office 365. Older Excel versions starting with Excel 2019 don’t have this function. There are other LOOKUP formulas to use that are explained in other articles which you can access from our blogs.

We recommend you use XLOOKUP instead of VLOOKUP in all future spreadsheets. If you have any questions, please feel free to ask us and share your experience in the comments section.  

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel training courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

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

How to Insert a Checkbox in Excel? 3 EASY Examples

Excel Bootcamp for Employees (100+ Best Courses)

Multiscreen Preview and Screen Size Settings in Dreamweaver CS6

How to Print Envelopes in Microsoft Word

Using the Windows 8 Firewall

Using Percentile Statistical Functions in Excel 2013

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)