This video will look into practical uses in Excel with the new XLOOKUP function, compare it to the VLOOKUP and HLOOKUP and see it’s practical applications in spreadsheets.

In the following screenshot, we use the VLOOKUP function. It is one of the most widely used functions in Excel. In this example, the VLOOKUP returns the Revenue number for Charlie. The VLOOKUP for Charlie should return 521.

The diffences with XLOOKUP

The key difference with the XLOOKUP, as opposed to the VLOOKUP and the HLOOKUP functions, is the use of a lookup_array and a return_array in the syntax – instead of a table_array.

Instead of the table_array syntax in a VLOOKUP, the XLOOKUP contains a lookup_array and a return_array. In the below screenshot example, the business unit “Name” column will be the lookup_array. Whilst the “Revenue” column will be the return_array.

In the following screenshots, we will look into practical uses of XLOOKUP in Excel. It can be used instead of VLOOKUP, in order to return the Revenue amount of 521 for “Charlie”.

In addition to this, there are 2 other differences with the syntax, in comparison to the VLOOKUP or HLOOKUP. These are:

1. The match_mode will use the Exact match option, and
2. The search_mode will apply the Search first-to-last variable

Here is a slight alternative, which will again return the correct amount of 521 using the XLOOKUP formula. For the match_model, this version of the XLOOKUP formula will instead use the “Exact match or next larger item” option.

In summary, here are the formulas to compare the VLOOKUP formula against the new XLOOKUP alternative to return Charlie’s Revenue of 521.

Practical Uses of XLOOKUP vs HLOOKUP

We will now compare the XLOOKUP versus the HLOOKUP. The HLOOKUP will locate the EBITDA amount for the Alpha business unit in Q2.

In summary, here is the formula of the HLOOKUP, which most users will be familiar with.

Let’s compare the XLOOKUP and see the difference.

As mentioned earlier, the key difference with the XLOOKUP is the use of a lookup_array and a return_array in the syntax – instead of a table_array.

In the above screenshot, the heading names in row 4 is the lookup_array. Whilst row 9 is the return_array because we want to return the EBITDA of Alpha in Q2. The match_mode will be the Exact match option, whilst the search_mode will be the Search first-to-last option

If the XLOOKUP formula is used correctly, the value returned will be 22. Let’s repeat this XLOOKUP.

As most Excel users know, one limit of VLOOKUP and HLOOKUP is the fact the exact match only returns the first value in an array.

However, the XLOOKUP enables you to return either the first or last value.