Explanation
In the example shown, cell G4 contains the lookup value, "Berlin". XLOOKUP is configured to find this value in the table, and return the population. The formula in G5 is:
=XLOOKUP(G4,B5:B18,D5:D18) // get population
- The lookup_value comes from cell G4
- The lookup_array is the range B5:B18, which contains City names
- The return_array is D5:D18, which contains Population
- The match_mode is not provided and defaults to 0 (exact match)
- The search_mode is not provided and defaults to 1 (first to last)
To return County instead of population, only the return array is changed. The formula in G6 is:
=XLOOKUP(G4,B5:B18,C5:C18) // get country
XLOOKUP vs VLOOKUP
The equivalent VLOOKUP formula to retrieve population is:
=VLOOKUP(G4,B5:D18,3,0)
There are a few notable differences which make XLOOKUP more flexible and predictable:
- VLOOKUP requires the full table array as the second argument. XLOOKUP requires only the range with lookup values.
- VLOOKUP requires a column index argument to specify a result column. XLOOKUP requires a range.
- VLOOKUP performs an approximate match by default. This behavior can cause serious problems. XLOOKUP performs an exact match by default.
Dynamic Array Formulas are available in Office 365 only.
Related formulas
XLOOKUP with multiple criteria
In this example, the goal is to look up a price using XLOOKUP with multiple criteria. To be more specific, we want to look up a price based on Item, Size, and Color. At a glance, this seems like a difficult problem because XLOOKUP only has one value for lookup_value and lookup_array . How can we...
XLOOKUP with logical criteria
XLOOKUP can handle arrays natively, which makes it a very useful function when constructing criteria based on multiple logical expressions. In the example shown, we are looking for the order number of the first order to Chicago over $250. We are constructing a lookup array using the following...
XLOOKUP lookup left
Whereas VLOOKUP is limited to lookups to the right of the lookup column, XLOOKUP can lookup values to the left natively. This means XLOOKUP can be used instead of INDEX and MATCH to find values to the left in a table or range. In the example shown, we are looking for the weight associated with...
INDEX and MATCH exact match
In this example, the goal is to look up various information about a random group of popular movies from the 1990s. The information to retrieve includes the year released, the rank against the other movies in the list, and worldwide gross sales. To retrieve this information, we are using an INDEX...
Related functions
XLOOKUP Function
The Excel XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal ranges....
