Since Microsoft launched its office suite back in 1990 to the present day, the list of improvements introduced has been very long. One of the latest, which came to improve the user experience of its calculation daughters program, was the introduction of the search function. In this post we are going to see what exactly is XLOOKUP and what is it for in Excel.
This feature was first integrated as a beta phase in August 2019. Currently, it is only available to users of Microsoft 365. It is a very useful tool for those who work in Excel with large volumes of data, as we show you below.
What is XLOOKUP?
Within Excel, the function SEARCHX belongs to the group of search and reference functions. Since it was implemented, it has become one of the most used tools by users due to its efficiency and ease of use.
XLOOKUP helps us to search for specific entries in a set of cells. A similar function called VLOOKUP existed before, although XLOOKUP works much better, since it allows us to search for multiple entries (with the previous function this was not possible) and to search for values vertically and horizontally.
These qualities open up new possibilities in the practical field. For example, using the XLOOKUP function on a table with numerous rows and columns will show us what we are looking for, regardless of where the information is located. The search, moreover, is not limited to a single page but to the entire document. And all of that runs with speed and precision. What more could you want?
Syntax of the XLOOKUP function
As every good Excel user already knows, each function has a specific syntax that must contain a series of specific parameters. You have to be very precise because any small inaccuracy when formulating the syntax can lead to an error. The syntax that must be applied to the XLOOKUP function is this:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The parameters corresponding to each of the values of this formula are the following:
- lookup_value: the input we're looking for, which can be made up of text, numbers, or even placeholders.
- Array_search: refers to the range or array in which Excel should search for the desired information.
- returned_array: refers to the range or matrix in which we want Excel to return the desired information.
These three are the basic parameters. There are also other optional parameters that we can use depending on each case:
- if_not_found: When Excel does not find a valid match, it can return a text or value that we have previously supplied to us (if this is not done, Excel will simply display the message "#N/A").
- match_mode: A type of match can be specified using the comparison mode.
- search_mode: You can trigger a binary search or determine where the search should start and end, using the value "1" for the first element and the value "-1" for the last.
Examples of using the XLOOKUP function
It is possible that all the information you have read up to here has left you a little confused. As always, it is better to resort to practical examples through which we will be able to better verify the usefulness of the XLOOKUP function in Excel:
Using only required parameters
First, the simplest case where you have to only the three mandatory specifications are used "lookup_value", "lookup_array" and "return_array".
In our example we present a table with the names of a group of basketball players and their height. The search result should be displayed in cell G6, which we must select. The formula that we must use is the following:
=BUSCARX(F6;B6:B17;D6:D17)
The searched value is “Galván” (cell F6). The array searched for is the column of the names (B6:B17), while the array returned is the column that corresponds to the heights of each one (D6:D17). Just press the “Enter” key to bring up the result we are looking for.
Adding two return values
We use the same example as in the previous case, only this time, in addition to the player's height, we want to know what his jersey number is. What needs to be done then is to expand the returned array so that Excel can find both results. The formula would look like this:
=BUSCARX(F6;B6:B17;C6:D17)
Adding the parameter "if_it_is_not_found"
Let's now imagine that, in the same example as in the two previous cases, we enter in our search the name of a player who does not appear on the list. It is here when the response text can be specified through the parameter "if_it_is_not_found". The chosen output text is "Invalid specification", although any other message that seems appropriate to us would also be valid. The corresponding syntax would be this:
=XLOOKUP(F6,B6:B17,C6:D17, "Invalid specification")
These are just three simple and practical examples of how to apply the XLOOKUP function in Excel. The truth is that this function can be used in much larger and more complex documents, thus obtaining the desired result quickly.