# THE XLOOKUP FUNCTION

Last you on **August 29**, two new important functions were announced: **XLOOKUP** and **XMATCH**. In this article, we will be talking about what the **XLOOKUP** function is, when and how to use it. When you start using this function, you will just not be able to let it go.

I want to mention a few things about **VLOOKUP** first: as you know, the **VLOOKUP** function was indispensable for many users, even those who didn’t know wanted to learn it. And even in the **7 Most Used Excel Functions** presentation **Microsoft** prepared, the first function is **VLOOKUP**.

As dispensable as it is for some users, it was useless for some others. Because **VLOOKUP** required some conditions to run and the function returned us the first match it found. On top of that, it decreased Excel’s performance since it could cause unnecessary calculation in the stated table range. It is the function for those who have unique data, but in a table with duplicate data -i.e. recurring data- it wasn’t very useful since it didn’t give us all the records. Of course, there are some methods to list all the records, but you either had to solve the issue by using auxiliary columns or list all the records with the **Array Formula**.

## LONG LIVE THE DYNAMIC FUNCTIONS!

Fortunately, in **September 25, 2018** the new dynamic array functions were announced and we got to breathe a sigh of relief. After waiting for such a long time, now the new function will be able to return a whole array instead of returning just one single result.

Instead of writing an **Array Formula** that was known as **“MULTIPLE VLOOKUP”** by everyone, we can list all the records of a data with the **FILTER** function easily. *We will be talking about the details in its own article. (Here are the article links of Microsoft, you can take a look.)

With the release of the **XLOOKUP** function, old functions like **VLOOKUP** and **HLOOKUP** are not needed anymore. And also, as they get into more detail and design what they can do, it seems like we will not need these functions:

**VLOOKUP > HLOOKUP > INDEX > MATCH > OFFSET > IFERROR(VLOOKUP)..** what else do you want?!👏🏻

Just one formula can:

- Look up both horizontally and vertically,
- Bring the first or last record, find the data considering the wildcard characters,
- Bring the data we want on the left without a condition like the
**lookup_value** has to be in the first column of the **table_array** in the **VLOOKUP** function, - Bring approximate vales in certain range like the
**TRUE** option in the **range_lookup** argument, - Allow you to say write this if no data is found without the need to use the
**IFERROR** formula, - Find the most approximate lowest or highest value if there is no exact match.
- And it offers all these in a way more faster way than before.

Enough with all the details, let’s see what this function does. 👍🏻

### THE XLOOKUP FUNCTION SYNTAX

**=XLOOKUP(***lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]*)

There are 6 arguments in this function.

The first 3 are required, the last 3 are optional.

Now let’s take a look at what these arguments mean, i.e. what the function wants from us and what we will give it.

Argument | Description |

**lookup_value** Required | The lookup value |

**lookup_array** Required | The array or range to search |

**return_array** Required | The array or range to return |

**[if_not_found]** Optional | Where a valid match is not found, return the [if_not_found] text you supply. If a valid match is not found, and [if_not_found] is missing, #N/A will be returned. |

**[match_mode]** Optional | Specify the match type: 0 – Exact match. If none found, return #N/A. This is the default. -1 – Exact match. If none found, return the next smaller item. 1 – Exact match. If none found, return the next larger item. 2 – A wildcard match where *, ?, and ~ have special meaning. |

**[search_mode]** Optional | Specify the search mode to use: 1 – Perform a search starting at the first item. This is the default. -1 – Perform a reverse search starting at the last item. 2 – Perform a binary search that relies on lookup_array being sorted in *ascending* order. If not sorted, invalid results will be returned. -2 – Perform a binary search that relies on lookup_array being sorted in *descending* order. If not sorted, invalid results will be returned. |

In the few examples below, you can see what you need to write when there is the first record and last record is not found of the looked up data and how to use it when looking up with wildcard characters.

Let’s have an example of the **Match Mode** argument with exact match or highest and lowest options.

So far we got the data from the column we wanted by looking up in the column just like in **VLOOKUP**. Now, let’s use this function like **HLOOKUP**.

Now, let’s have a 2 dimension look up. Let’s match based on both the Product name and Month, and find the data in the intersecting cell. Users who know how to use the **INDEX** and **MATCH** functions usually do the matching we are about to the with the **INDEX + MATCH + MATCH** formulas. Now, let’s see how it is done with **XLOOKUP**.

Lastly, let’s compare the **VLOOKUP** and **XLOOKUP** function while finding the same data. At first, you will see that you get the same result, but when we add or delete a column, the **VLOOKUP** function will give us an incorrect result. But the result will not change in the **XLOOKUP** function. The reason is this: Since we write the column number that we want to get manually in **VLOOKUP**, the **col_index_num** stays stable but the column of the data changes and it returns an incorrect result. And in **XLOOKUP**, it doesn’t matter how many columns you add or delete, since we stated the column we want, that column changes dynamically and returns the correct result without producing any errors.

You can see the result in this example.

I hope this was helpful.. 👍🏻

You can share this post and help many others get informed as well. Keep in mind that information gets more valuable when it is shared.