Site icon Professor Excel

Advanced XLOOKUP: All the great, fancy stuff possible with XLOOKUP!

In our previous article of this big XLOOKUP series, we have explored the basic usage of XLOOKUP. Now it is time to up our game: All the great, fancy stuff you can do with XLOOKUP. We look at error catching, wildcard lookups and grouping / classification problems. These advanced XLOOKUP functions will make you the lookup master!


Overview of the big XLOOKUP series


Also for our second part of the series, some words as an introduction.

Full XLOOKUP syntax

Recap first three arguments

The XLOOKUP formula can have up to six arguments. Here are the first three arguments again (for more details please refer to this article).

  1. The first argument is the search or lookup value. It’s the value you look for in your Excel table.
  2. The search area is a range of cells (e.g. row or column) you want to find your search value in.
  3. Return area is also the range of cells you want to have a value returned from.

Advanced XLOOKUP arguments

  1. IF NOT FOUND: If the search value was not found, this argument is returned. This part works similar to the IFNA function. So, if you want, you could consider the IFNA() function right inside the XLOOKUP. No need, to wrap it around any longer.
  2. MATCH MODE: With the match mode you can define in what case Excel should find your search value.
    1. Exact match: 0 (is also the default value if you leave it blank)
    2. Get an exact match or next smaller item: -1
    3. Exact match or next larger item: 1
    4. Wildcard character match: 2
  3. SEARCH MODE: The search mode determines the direction, the XLOOKUP searches for the values.
    1. The default value: Search for first-to-last: 1
    2. Search for last-to-first: -1
    3. Binary search (sorted in ascending order): 2.
      Important: this only works if you data is sorted in ascending order. If not sorted, invalid results will be returned.
    4. Binary search (sorted in descending order): -2.
      Important: this only works if you data is sorted in descending order. If not sorted, invalid results will be returned.

Advanced XLOOKUP example 1: Value not found

Let’s start with an easy example: You want to return “Name not found”, if a lookup value can’t be found in the search area. Assuming the following table: You have sales person names in column B and their amount sold in column C. In cell F4, you want to return the amount sold depending on the name entered in the cell above, F4.

Advanced XLOOKUP: The forth argument of XLOOKUP works like the IFNA function. It defines the return value in case the search term was not found.

The basic lookup is quite straight-forward:

  1. Fill in the search value = F3.
  2. Next, the search area, in this case column B. So, the second argument is “B:B”.
  3. The third argument contains the return value, in this case the amount sold, column C.
  4. Now, we want to extend the function to return “Name not found” in case the name was either typed incorrectly or the name is not within the list. And that’s already the forth argument – just type the return value if not found.
    =XLOOKUP(F3,B:B,C:C,“Name not found”)

Of course, a IFNA function wrapped around would serve the same purpose. But why use an additional, nested function, if XLOOKUP offers this functionality right built-in?

Advanced XLOOKUP example 2: Wildcard lookups

Our next advanced XLOOKUP example is about wildcard characters. Wildcard means that you do not specify a part of the search value (or the complete search value). For example, finding everything starting with “a”.

An example closer related to XLOOKUP: You want to search for “Victoria” but you are not sure if its written “Victoria” (with “c”) or “Viktoria” (with “k”). In such case, you could search for “Vi?toria”. So, the “Victoria” example would look like this:

Wildcard searches in XLOOKUP are also possible. But careful: The fifth argument must be “2”.

Excel has two wildcard characters (“?” and “*”) as well as the tilde (“~”) in case you actually want to search for a question mark or asterisk. These characters don’t only work for XLOOKUP, but also for VLOOKUP(), any other lookup function (such as MATCH(), SUMIFS()), and the Excel search feature.

Wildcard characterExplanation
? (question mark)Use a question mark for any single character.

For example, sm?th finds “smith” and “smyth”.
* (asterisk)Adding the asterisk finds any number of characters.

For example, *east finds “Northeast” and “Southeast”.
~ (tilde) followed by ?, *, or ~You actually want to search for the asterisk or question mark? Put a tilde in front.

A question mark, asterisk, or tilde
For example, fy06~? finds “fy06?”.
Overview of wildcard characters in Excel.

Explicitly specify a wildcard XLOOUP

We now know, what is a wildcard. Before we take a look at another example, there is one thing to mention: Unlike VLOOKUP, MATCH etc., you have to specify in XLOOKUP explicitly that you conduct a wildcard lookup. You do this in the SEARCH MODE argument. That means, in the fifth argument, the SEARCH MODE, you enter “2”.
If you don’t put “2” into the fifth argument and start a wildcard search, Excel conducts an exact search. That means, =XLOOKUP(“Viktoria*”,B:B,C:C) searches exactly for “Viktoria*”, including the asterisk. It is as if you’ve entered a tilde before the *.

Example for wildcard XLOOKUP

Not it’s time to finally look at a wildcard XLOOKUP example: You have a table containing sales persons with two columns, B and C. Column B has the full names, including first- and last names. The task: You only want to type the first name in cell F3 and return the amount in EUR to cell F4.

Example: Return the amount of sales person “Tom Bellwood” just by searching for “Tom” in the wildcard XLOOKUP function.

Let us set up the XLOOKUP function step-by-step:

  1. The first argument is the search value. We search for the value given in cell F3, so it should be “B3″. But because we only want to type the first name and not the full name, you add &”*” to this argument.
    With the “*” you search for anything starting with “Tom”, in this case.
  2. We search within column B, so the SEARCH AREA is B:B. You could also specify it more exact, like B4:B14, but let’s keep it simple here. =XLOOKUP(F3&”*”,B:B,
  3. Return value is in column C, so the third argument is “C:C”
  4. The fourth argument we skip here. It’s the “if not found” value and we don’t have to set it.
  5. And last – that’s important – we have to specify the wildcard match. So, the fifth argument has to be 2.
Solution: Don’t forget to put “2” into the fifth argument of this advanced XLOOKUP.

Advanced XLOOKUP example 3: Group classification problem using the “next smaller item” XLOOKUP match mode

Example: Fill the group name into column D

In this example, we want to classify data by its amount into groups. Let’s assume the following scenario: Depending on the sales amount in column C, you want to add the group name (e.g. XS, M, L) from column G.

So, if the amount is between 0 and 200, “XS” should be the group name. For amounts between 200 and 400, the group is “S” and so on.

Example: You want to insert the group name depending on the amount into column D.

The solution: Step-by-step

Again, we assemble the XLOOKUP function step-by-step for the function in cell D4.

  1. The first argument is the search value. Because the classification is done by the amount, the first argument of XLOOKUP is C4.
  2. Also, this argument should be simple: You look for the value in the cell range F4 to F9. Because we later want to copy this function down to the cells below, we right away insert $-signs in order to convert the range to an absolute reference.
  3. Our desired return value should be the group name. That’s why the third argument, the return area, is the group name given in cells G4 to G9.
  4. The forth argument defines the value if nothing can be found. We can leave it blank here.
  5. Now comes the trick: The fifth argument defines the “MATCH MODE”. Because we don’t search for an exact match (that would mean the value must be exactly 0, 200, 400, etc.) but rather previous, smaller value, the match mode is -1.
    To put it in slightly different words: If we search for 260 (which is not like this in the list), the return value should be “S”. So, we search for an “exact match or next smaller item”.
Solution: The trick is to put -1 into the fifth argument of the advanced XLOOKUP function so that it searches for the “next smaller item”.

Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!

Additional comments to advanced XLOOKUP classification


Please download the example from above in this workbook. It also contains the basic examples from our first part of this series. Click here to start the download.

Exit mobile version