Excel XLOOKUP Demo: Complete Guide

Question 1:

Use XLOOKUP to perform a one-way vertical lookup for a Business ticket from Kansas City to Philadelphia. A vertical lookup searches for a value in a column and returns something from a different column, similar to a VLOOKUP, while a horizontal lookup searches a row and returns something from a different row (like an HLOOKUP).

Let’s start by selecting the route, "Kansas City to Philadelphia." I’ll type in the XLOOKUP function and press Tab. The lookup value will be the selected route, and the lookup array will be where we find this value. This terminology is similar to the MATCH function, where we have a lookup value and a lookup array. Next, we need to choose a return array, which will contain the ticket prices—in this case, the Business ticket prices.

Normally, with the MATCH function, we'd specify 0 for an exact match, but in XLOOKUP, an exact match is the default. There are other match types (like -1 or 1), which we’ll explore in the next question. For now, I’ll leave that blank, close the brackets, and press Enter. The result is £742, which is the price for a Business ticket from Kansas City to Philadelphia.

Question 2:

Use XLOOKUP to perform a one-way horizontal lookup for an Economy ticket from Las Vegas to Houston.

First, I’ll pick "Economy" as the class. I’ll type in XLOOKUP and press Tab. The lookup value will be "Economy," and the lookup array will be the three classes (Economy, Premium, and Business). The return array will be the prices for "Las Vegas to Houston." After closing the brackets, it returns £156, which is the price for an Economy ticket on that route.

Question 3:

Use XLOOKUP to perform a two-way lookup for a Premium ticket from Minneapolis to Pittsburgh.

In INDEX and MATCH, this would require using both a row and column match to return the appropriate value. However, with XLOOKUP, we can embed one lookup inside another. I’ll start by choosing the route, "Minneapolis to Pittsburgh," and the class, "Premium."

I’ll type out the XLOOKUP function. The first lookup value will be "Minneapolis to Pittsburgh," and the lookup array will be the column containing the routes. Instead of choosing a specific column for the return array, I need to perform another lookup to identify the appropriate class. I’ll insert another XLOOKUP function, where the lookup value will be "Premium," the lookup array will be the class names, and the return array will be all the ticket prices.

Closing the brackets for both XLOOKUP functions and pressing Enter, we get £277. This means XLOOKUP first found "Minneapolis to Pittsburgh" and then identified the Premium price as £277. Conceptually, this is slightly trickier than INDEX and MATCH, as the second XLOOKUP returns an array of figures, which the first XLOOKUP then uses.

Question 4:

Use XLOOKUP to perform a one-way lookup for a Premium ticket on a route that begins with "San."

I’ll type "San" and note that there are multiple routes beginning with "San," such as San Francisco, San Diego, and San Jose. I’ll input the XLOOKUP function. The lookup value will be "San," the lookup array will be the routes, and the return array will be the Premium ticket prices.

When I close the brackets, it returns #N/A because there isn’t a cell with just "San." To handle this, I can add an "if not found" clause by typing a comma and specifying what should happen if the lookup value isn’t found. For instance, I could return "Not Found." Now, if XLOOKUP doesn’t find "San," it will return "Not Found."

Question 5:

Use XLOOKUP with a wildcard match for a Premium ticket on a route that starts with "San."

I’ll type "San" again and use a wildcard in the XLOOKUP function. For the lookup value, I’ll concatenate "San" with an asterisk to allow for any characters following "San." The lookup array will be the routes, and the return array will be the Premium ticket prices.

After adding the wildcard match mode and closing the brackets, it returns £501, which is the price for a Premium ticket from "San Francisco."

Question 6:

Use XLOOKUP to perform a one-way lookup using a wildcard for "San" but return the price for a route from San Jose to Seattle, searching from the bottom up.

I’ll type "San" again and use the XLOOKUP function with the wildcard. The lookup array will be the routes, and the return array will be the Premium ticket prices. To search from the bottom up, I’ll change the search mode to "last to first" by setting the search mode to -1.

Pressing Enter returns £566, the Premium ticket price for "San Jose to Seattle." This differs from the default search, which would return the first match from the top.

Question 7:

Use XLOOKUP to perform a one-way lookup using a wildcard for a Premium ticket from Nashville to San Antonio.

The route, "Nashville to San Antonio," is below "San Jose to Seattle." When using XLOOKUP with a wildcard, it defaults to searching from the top down, so it might return an earlier match. To find the correct route, I’ll modify the search criteria to account for text both before and after "San."

After adjusting the search mode, it correctly returns the price for "Nashville to San Antonio." If we don’t specify the search order, it would return the price for "San Francisco to Miami" instead, as it’s the first match from the top.