Exceljet

Quick, clean, and to the point

Basic XLOOKUP example

In this video, we’ll set up the XLOOKUP function with a basic example.

The XLOOKUP function is a more flexible replacement for VLOOKUP, and it's just as easy to use.

In this worksheet, we have population data for some of the largest cities in the world.

Let's configure the XLOOKLUP function to retrieve the country and population for a given city by matching on the name.

To start off, put the cursor in G5, enter an equals sign (=) and type "xl". This is enough to match XLOOKUP. I'll press Tab to confirm.

The first argument for XLOOKUP is the lookup value. For this, we want to use the city name in G4.

The next argument is lookup_array. This is the range that contains the value we want to look up.

Unlike VLOOKUP, where we'd want to supply the entire table, with XLOOKUP, we only supply the range for city, in B5:B18.

Next, we need to provide a range for return_array. This is the range that contains the value we want to retrieve. In this case that's the country names in C5:C18.

XLOOKUP only requires these 3 arguments.

If I like, I can provide a message to display when XLOOKUP finds no match. Here I'll use "No match" in double quotes.

XLOOKUP automatically performs an exact match, and automatically starts at the top of the data.

These defaults work just fine in this example, so we don't need to set values for match mode or search mode.

When I enter the formula, the result is Germany, which is correct.

To retrieve population, I can use exactly the same formula, copied down with control + a single quote, than I just need to change the return array.

Instead of Country, I want Population, which is D5:D18. 

Now if I change the City name to a different value, say Tokyo, XLOOKUP will retrieve the correct data.

And if I try a city that doesn't exist in the source data, XLOOKUP will return the "no match" message I provided.

Dynamic Array Formulas are available in Office 365 only.

Related shortcuts

Author 
Dave Bruns