XLOOKUP: The New Excel Function that Replaces VLOOKUP

XLOOKUP: Meet the New Boss

XLOOKUP is a brand new Excel function introduced in 2020 that replaces the old VLOOKUP and HLOOKUP functions. VLOOKUP is the 3rd most popular Excel function after SUM and AVERAGE.

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

VLOOKUP will replace:

  • VLOOKUP
  • HLOOKUP
  • INDEX/MATCH

Introducing XLOOKUP

VLOOKUP debuted in Excel 1.0 in 1985. It copied the old Lotus 1-2-3 function @VLOOKUP. It now runs in billions of Excel spreadsheets. But now VLOOKUP is outdated. There is a new Excel boss!

Excel Basics: VLOOKUP Tutorial for Excel

XLOOKUP is only available in Office 365. Microsoft releases all new Excel features in Office 365.

To access the new functions, users must subscribe to Office 365. Educational users can subscribe for free using their university email.

It replaces VLOOKUP

Excel users should start replacing VLOOKUP and HLOOKUP with the new XLOOKUP for all new spreadsheets. It is easier to use and it is much simpler to understand. It will take time for XLOOKUP to become as popular as VLOOKUP, so you cannot abandon it completely.

However, not all versions of Excel will have access to XLOOKUP. It is in the Office 365 version of Excel. If you need a spreadsheet to work under a previous version of Excel, you must stick with VLOOKUP. The change from VLOOKUP will take time.

Know your Excel: 10 Excel Functions Everyone Should Know

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 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)

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