which returns 3,517,424, the population for Berlin from column D.
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 vs VLOOKUP
The equivalent VLOOKUP formula to retrieve population is: 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.
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.
