Exceljet

Quick, clean, and to the point

XLOOKUP basic exact match

Excel formula: XLOOKUP basic exact match
Generic formula 
=XLOOKUP(value,rng1,rng2)
Explanation 

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:

=XLOOKUP(G4,B5:B18,D5:D18)

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

XLOOKUP is a beta function, available only through the Office Insiders program.

How this formula works

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.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.