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 then press the Tab key 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 for 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, then 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
