You can download all the examples plus tips & tricks all in one file by clicking here.

Real life examples

We’ll be using a subset of 2015 World Happiness Index data.

Example 1 – Vertical Lookup

We’ll start by using only the required arguments: lookup_value, lookup_array, and return_array to find the happiness score of the country of Belgium. This is an example of a vertical lookup using XLOOKUP.

=XLOOKUP(H2, A2:A20,D2:D20)

Here we have used the value in cell H2 as our lookup_value (Belgium). We have then selected the range of A2:A20 as our lookup_array and the range D2:D20 as our return array.

XLOOKUP will look for the first instance of Belgium (H2) in the column of countries (A2:A20). Here Belgium is the 19th country. XLOOKUP will then return the 19th value in the Happiness Score column (D2:D20).

Example 2 – Horizontal Lookup

For this example, we will temporarily use a subset of our data arranged in a horizontal way for the purpose of demonstration.

Now if we want to select the happiness score of the country of Belgium, we’ll use the following formula:

=XLOOKUP(J2, B1:H1, B3:H3)

Here we have used the value in cell J2 as our lookup_value (Belgium). We have then selected the range of B1:H1 as our lookup_array and the range B3:H3 as our return array.

XLOOKUP will look for the first instance of Belgium (J2) in the countries row (B1:H1). In our example Belgium is in the H column. So XLOOKUP will return the H value of the Happiness Score row (B3:H3).

Example 3 – if_not_found argument

We’ll now introduce our first optional argument: if_not_found.

The if_not_found argument is a text that will be returned if a matching lookup_value could not be found in the lookup_array.

Let’s say we want to find the happiness of score of the country of United Kingdom from our table. If we use the arguments from example 1, we’ll get the following result:

=XLOOKUP(H2, A2:A20, D2:D20)

But what if we want to provide a more descriptive result in case a value is missing from the table? Like for example we want a message of “Country not found” to be shown. In this case we’ll use the if_not_found argument.

=XLOOKUP(H2, A2:A20, D2:D20, “Country not found”)

Here XLOOKUP will look for United Kingdom (H2) in the Country column (A2:A20), and since United Kingdom is missing the message “Country not found” will be returned.

Example 4 – match_mode argument

Next in our list of optional arguments is: match_mode.

When XLOOKUP can’t find the lookup_value in the lookup_array, it will either return N/A or the if_not_found arugment if the argument is provided to the formula. But with match_mode you can find the closest value to the lookup_value if the lookup_value is missing. We’ll explore this concept now with an example.

Let’s say given our data table you’re looking for a country with the happiness score of 6. If you use the argument from example 1, you’ll get N/A.

=XLOOKUP(H2, D2:D20, A2:A20)

Or if you use the if_not_found argument in as well like example 3, you’ll get “Score not found”.

=XLOOKUP(H2, D2:D20, A2:A20, “Score not found”)

But you provide the match_mode argument (for example 1), you’ll get Belgium.

=XLOOKUP(H2, D2:D20, A2:A20, “Score not found”, 1)

Here the XLOOKUP will look for a score of 6 (H2) in the Happiness Score column (E2:E2) but cannot find it. Since we have set the match_mode to 1 (1 means next largest item if an exact match is not found), XLOOKUP will look for next value in the Happiness Score column (E2:E20) that is greater than 6 (H2) and finds the cell D20 which is the 19th item in the lookup_array. Next, XLOOKUP will return the 19th element in the return_array (A2:A20) which is Belgium.

To refresh your memory, there are 4 arguments for match_mode:

•  0 – which means an exact match. And if an exact match is not found, N/A or if_not_found argument is returned.
• 1 – means in case an exact match is not found, the next largest item is returned.
•  -1 – means in case an exact match is not found, the next smallest item is returned.
•  2 – mean a wild card match .

Example 5 – search_mode argument

The last optional argument is: search_mode.

The search_mode argument specifies how will XLOOKUP search the lookup_array. The arguments for search_mode are the following:

• 1 – XLOOKUP perform a search starting at the first item – This is the default.
• -1 – XLOOKUP perform a reverse search starting at the last item.
• 2 – XLOOKUP perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.
• -2 – XLOOKUP perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

Let’s say you wanted to find which Latin American or Caribbean country in our subset data is the least happy country based on its Happiness Score. You’ll write the following:

=XLOOKUP(H2, B2:B20, A2:A20, “Region not found”, 1, -1)

Here the XLOOKUP will search for the first instance of Latin America and Caribbean (H2) in the Region column (B2:B20) starting from the bottom which result is the 5th item from the bottom. Next XLOOKUP will return the 5th item from the bottom in the Country column (A2:A20) which is Brazil.

Tips & Tricks #1 – Returning multiple Values

XLOOKUP can return multiple values across multiple rows and columns. You can return multiple values using two ways:

1 – By using range operator “ : “

=XLOOKUP(H2, A2:A20,B2:B20:C2:C20)

Here XLOOKUP will first look for Iceland (H2) in the Country column (A2:A20) which is the 2nd item in the Country column, next XLOOKUP will look for the 2nd items in the Region column (B2:B20) and the Happiness Rank column (C2:C20) and returns them in two separate columns. We achieved this by using a range operator between the two column ranges B2:B20:C2:C20. We could have achieved the same result if we used B2:C20.

Note that in this method the results are from columns that are next to each other. To return items from multiple columns that are not next to each other we have to use the concatenate operator.

2 – By using string concatenate operator “ & “

=XLOOKUP(H2, A2:A20,B2:B20&C2:C20)

Again, XLOOKUP will first look for Iceland (H2) in the Country column (A2:A20) which is the 2nd item in the Country column, next XLOOKUP will look for the 2nd items in the Region column (B2:B20) and the Happiness Rank column (C2:C20) but this time the results of these ranges are concatenated together and returned in a single column together. You can also make the result more readable by concatenating another string (for example “, “).

=XLOOKUP(H2, A2:A20,B2:B20&”, “&C2:C20)

Tips & Tricks #2 – Using multiple inputs

For this trick we’ll first introduce an extra column to our table. “GDP per Capita Rounded” This column is the GDP per Capita column but rounded to 2 decimal places so that multiple countries would have similar GDP for the purpose of demonstration.

XLOOKUP accepts multiple inputs and can assess them across multiple columns.

=XLOOKUP(I2&J2, B2:B20&E2:E20, A2:A20)

Here, for lookup_value we have concatenated Region (I2) and GDP (J2). And for lookup_array, we have concatenated Region column (B2:B20) and GDP per Capita Rounded column (E2:E20). XLOOKUP will look the Western Europe in Region column and 1.46 in GDP per Capita Rounded column which is the 4th row. Next from our return_array which is set to Country column (A2:A20) XLOOKUP will return the 4th item, and that is Norway.

Tips & Tricks #3 – Using a nested XLOOKUP

If you know how to write proper nested XLOOKUP, you have mastered this dynamic array function. Nested XLOOKUP is often used to dynamically select a column and return a single important data point. For example: Let’s say you want to know the Region of the country of Mexico, you can return a simple XLOOKUP and get the results you want. However, what if you now change your mind and what to see the happiness rank of Mexico? You can either change your XLOOKUP or write a new XLOOKUP. Or you can write a nested XLOOKUP to dynamically change the criteria you’re looking for. This way you don’t need to change your arguments or write a new one. Let’s see this concept in action:

=XLOOKUP(H2,A2:A20,XLOOKUP(I2,B1:F1,B2:F20))

To better understand what is going on let’s break down our formula step by step. We have two XLOOKUPs, an outer XLOOKUP, and an inner XLOOKUP. We have to start with the inner XLOOKUP:

Here Happiness Score (J2) is our lookup_value and our column headers (B2:F2) are our look_array, our entire table aside from the Country column is our return_array (B2:F20). Happiness Score is the 3rd item in the column headers, XLOOKUP will then return the 3rd column from the return_array, which consists of all the happiness scores of all the countries.

Next Let’s take a look again at the outer XLOOKUP and explain what is going on.

Here XLOOKUP will search for Mexico (I2) in the Country column (A2:A20). Mexico is the 14th country in lookup_array. XLOOKUP will then return the 14th item from the result of inner XLOOKUP which was a list of happiness scores of all the countries.

Now if we change Happiness Score (J2) to “Region” or any other column name, XLOOKUP will return the correct result every time. We can even keep Happiness Score and change the country. Nested XLOOKUP gives analysts so much more flexibility with very little effort.