For search tasks, in addition to using the VLOOKUP function, we can also use the XLOOKUP function! Let's see how it works!

Our table contains clients of a credit institution. We see the clients' names, IDs, net salaries, and all other loan-related data. On the following worksheet, some client IDs are highlighted in column A. First, let's display the names corresponding to these IDs. We can't use VLOOKUP here, because if we look at the client list on the worksheet, it's clear that the IDs are in column B, while the names are in column A, to the left of the IDs. Let's solve the task with XLOOKUP, insert it in cell B2! The Lookup value, as with VLOOKUP, is the ID in cell A2.

However, a significant difference between the two functions is that we must specify separately the column in which we search and the column from which we expect the result.

This is the advantage of XLOOKUP, as it makes the order of columns irrelevant. So, the Lookup array comes next. This is the column where we search for IDs, namely column B of the List of customers worksheet. It's advisable to select entire columns, especially for large tables. If we do choose a range, remember to fix it! Then, specify the Return array, from which we expect the result. This is column A of the List of customers worksheet. XLOOKUP inherently performs an exact search; we don't need to set this separately. Leave the other arguments empty for now; they are not needed for this task. We're done, copy the formula down!

For one ID, we get an error message, meaning this ID is not in the client list table.

Next, let's also display the net salaries of the highlighted clients in column C! The net salary in the base table is to the right of the IDs, so VLOOKUP would work, but let's use XLOOKUP again.

The Lookup value is the first ID, the Lookup array is column B of the List of customers worksheet, and we expect the result from column C. For the second ID, we would again get an error message, but let's handle that now. Use the XLOOKUP If not found argument! If the formula encounters an error during the search, it will output the value we enter in this field. Let's set it as the word "none".

Finally, let's also display the transaction type, create the formula similarly as before.

XLOOKUP is also suitable for horizontal searches. The trick is to select a row instead of a column for the two arrays. Let's look at the next two worksheets!

The table here contains exchange rates for 6 different banks. On the other worksheet, we see the same currencies in the same order.

First, calculate in column B the most favorable, i.e., the lowest exchange rate for each currency. This can be solved with a simple MIN function.

Then, in column C, display which bank offers these most favorable values. So we need to search for the given value in the corresponding row of the previous table and display which bank it belongs to.

Insert the XLOOKUP function into cell C2! The Lookup value is the exchange rate in cell B2. We search for this in row 2 of the base table, expecting the result from row 1.

Remember, we'll copy the formula down, so think about where we need dollar signs. One thing must not shift, namely the return array. We always expect the result from there.

We are already familiar with the XLOOKUP function, having used it several times for precise searches. In the next step, we will perform an exact search again, but this time only a part of the value we are searching for is available to us.

Let's look at the following table! This range contains the clients of a financial institution. We see the clients' names, IDs, net salary, and all other data related to the loan. On another worksheet, we see some IDs; based on these, we want to display the necessary data from the other table. The problem is that only part of the original IDs are stored here.

To solve this, we use the XLOOKUP function again. To search based on a word fragment, we will need the asterisk character, which substitutes any length of character string, and we need to change the operating mode of the function. For this, we will need the Match mode field.

Insert the XLOOKUP function into cell B2! First, we specify the Lookup value, which is still cell A2. We will search for this in the other table's column B, but here only fragments of the IDs in column B appear.

So, other text parts may appear before and after the value in cell A2. We substitute these with the asterisk character. Concatenate the asterisk to the front and end of the cell reference in A2. Be careful! The asterisk is just like any other character, don't forget to put it in quotes. We use the & symbol for concatenation now. Specify the Lookup and Return arrays in the usual way.

Finally, let's look at the Match mode! The default value of this field is 0, which means plain, simple exact search, which we don't need to specify separately. However, if we perform the search based on a word fragment, then we should write 2 here. We are done, copy the formula to the other rows as well! Use the same method to display the other data in columns C and D!