Skip to content

The New and Modern XLOOKUP Function

One of the newest functions introduced in Excel 365 and Google Sheets is the XLOOKUP function.

This function takes a more intuitive approach to look up values in your data, and it’s a great replacement for the VLOOKUP and HLOOKUP functions.

The XLOOKUP Function

The XLOOKUP function allows you to find a piece of data that is related to some other data. Take a look at the example below.

Here, you have various foods and prices. If you want to look up the cost of a specific food, you can use the XLOOKUP function.

How the XLOOKUP Function Works

The XLOOKUP Function can accept up to six arguments, but only three are required. The arguments for the XLOOKUP function are as follows.

=XLOOKUP(Lookup_value, Lookup_array, Return_array, If_Not_Found, Match_mode, Search_mode)
  • Lookup_value – This is the value you are searching for. In the previous example, this was the specified food. (Required)
  • Lookup_array – This is the range of cells you want the function to look through to find the Lookup_value. In our previous example, this was the list of foods. (Required)
  • Return_array – This is the range of cells you want the function to look through to get the final return value. In our previous example, this was the list of prices. (Required)
  • If_not_found – This is what the function will return if it cannot find the lookup value. If this argument is not specified, XLOOKUP will return an error if it cannot find the lookup value. (Optional)
  • Match_mode – This determines what type of match the function will perform. This argument can be -101, or 2. If you want to learn more about the various match modes, let us know in the comments, and we will write a follow-up article.
  • Search_mode – This determines what type of search the function will perform. This argument can be -2-11, or 2. If you want to learn more about the various search modes, let us know in the comments, and we will write a follow-up article.

Given these arguments, the XLOOKUP function looks through the Lookup_array to find the Lookup_value.

When it finds the Lookup_value, the XLOOKUP function takes note of the position.

The XLOOKUP function then goes through the Return_array and returns whatever value exists in the position it calculated earlier.

If the XLOOKUP function cannot find the Lookup_value in the first place, then it simply returns the If_not_found value.

To better understand the XLOOKUP function and its arguments, take a look at the following example.

XLOOKUP Example

This example will only use the first four arguments of the XLOOKUP function. Take the list of names and colors below.

The goal is to figure out the last name of the person who has a favorite color of blue. In other words, given the favorite color (lookup_value), you want to find the last name (return_value).

Here is how you use the XLOOKUP function to accomplish this task.

The first argument is the value you are searching for, which is the favorite color in this case.

The second argument is the list you want the function to search through to find that color.

The third argument is the list of names you want the function to look through to return the final value.

The fourth argument is what you want the function to return if it cannot find the specified favorite color.

The XLOOKUP function does its thing and returns Norman because this is the last name of the person whose favorite color is Green.

If you were to have the XLOOKUP function search for the color Gold, it would return Cannot Find because Gold is not among the list of colors.

And that is how the XLOOKUP function can look up and return related pieces of data. It’s a little more intuitive and powerful than the former VLOOKUP function!

This Post Has One Comment

  1. I love xlookup! I use it all the time at work! I never have explored the “match_mode” or “search_mode” before, but I really want to know more about it! A follow up article would be great!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top