Excel XLOOKUP Tutorial for Beginners

xlookup tutorial

XLOOKUP function searches a range for a match and returns an item from a second range. It can search both vertically or horizontally. XLOOKUP replaces both VLOOKUP and HLOOKUP.


XLOOKUP in Excel 365

XLOOKUP is a new Excel function introduced in 2020. It is available in Excel 365. XLOOKUP is not available in Excel 2016 or Excel 2019.

The three most popular Excel functions are:

  1. SUM
  2. AVERAGE
  3. VLOOKUP

XLOOKUP replaces the following Excel functions:

  • VLOOKUP – vertical search
  • HLOOKUP – horizontal search
  • INDEX and MATCH – column and row search

XLOOKUP replaces VLOOKUP

VLOOKUP debuted in Excel 1.0 in 1985. It copied the old Lotus 1-2-3 function @VLOOKUP. VLOOKUP runs billions of Excel spreadsheets. But now VLOOKUP is outdated. XLOOKUP is better than VLOOKUP.

See Excel VLOOKUP Tutorial

Excel users can replace VLOOKUP and HLOOKUP with XLOOKUP for all new spreadsheets. Here are some benefits of XLOOKUP over VLOOKUP:

  • easier to use
  • simpler to understand
  • “not found” does not create an error
  • horizontal search: top to bottom and bottom to top
  • vertical search: left to right and right to left

Learn Excel: Ultimate Guide to Learn Excel Basics


XLOOKUP Syntax

XLOOKUP searches a range for a match and returns an item from a second range. By default, it uses an exact match.

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

  • Lookup_value: value to search for
  • Lookup_array: range to search
  • Return_array: range to return
  • If_not_found: text returned if no match is found. [Optional]
  • Match_mode: specify how to match lookup_value against the values in lookup_array [Optional]
    • 0 – exact match (default)
    • -1 – exact match or next smaller item
    • 1 – exact match or next larger item
    • 2 – wildcard character match
  • Search_mode: specify the search mode to use [Optional]
    • 1 – search first to last (default)
    • -1 – search last to first
    • 2 – binary search (sorted ascending order)
    • -2 – binary search (sorted descending order)

What is it used for?

XLOOKUP searches a range for a match and returns the corresponding item from a second range. Unlike VLOOKUP, it uses an exact match. This solves a weakness in VLOOKUP. Also, it can search both columns or rows.

It can retrieve a description or a price by looking up a part number. XLOOKUP also can find a grade by giving a student id number. It can retrieve either horizontally or vertically. VLOOKUP can’t do that!

XLOOKUP video tutorial

So, do you need a video tutorial about the new XLOOKUP function? I created this XLOOKUP tutorial.


Try it out yourself

Try the new XLOOKUP function. I think you will like it and add it to your Excel toolkit. It has several advantages over the older functions.

Try to replace a VLOOKUP or an HLOOKUP with the XLOOKUP function. It is a better tool than the older functions.

Jeff Mankin

Jeff Mankin teaches financial literacy. His website is FinallyLearn.com.

Recent Posts