To use XLOOKUP to find an exact match, you'll need to supply a lookup value, a lookup range, and a result range. In the example shown, the formula in H6 is:


which returns 3,517,424, the population for Berlin from column D.

Generic formula



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


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.
Dynamic Array Formulas are available in Office 365 only.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.