※This document is an English translation of this blog

Conclusion.
Let’s set the 5th argument to “2

Relatively new function XLOOKUP
It is an enhanced version of VLOOKUP and is very easy to use.
On the other hand, it requires some tricks when performing fuzzy searches (searches using wildcards*), so I will explain them below.

Basic FormXLOOKUP (search value,search range,result range)
The fourth argument is what to do if the result is not found.
Main Question.When performing fuzzy searches, the fifth argument should be 2

Basic FormXLOOKUP (search value,search range,result range)

First, the above is the basic form.
Let’s look at the specifics.
If you want to display the amounts corresponding to “mandarin oranges” from the following table
(1) Search value: mandarin oranges
(2) Search range: C3:C5
(3) Result range: D3:D5
Since the result will be

=XLOOKUP("みかん",C3:C5,D3:D5)

Enter

The fourth argument is what to do if the result is not found.

Continue,

=XLOOKUP("mandarin",C3:C5,D3:D5,this)

then we get the following result

Main Question.When performing fuzzy searches, the fifth argument should be 2

First, please see the following images

Why do I get an error when I use a wildcard () in the search value? This is the point where people who are familiar with VLOOKUP stumble. If you want to use a wildcard () as a search value in XLOOKUP, do the following.

=XLOOKUP("*mi*",C3:C5,D3:D5,"",2)

If you set the fifth argument to “2”, you can perform a fuzzy search.

The XLOOKUP function is a very useful function, but there are some points to keep in mind when performing fuzzy searches.
Please use it to improve your work efficiency!

Thank you very much for reading this article!