Spreadsheet Practice

Lesson 13 of 45, Lookups

XLOOKUP

Quick answer

The modern lookup: any direction, exact by default, with a fallback.

Key formula
=XLOOKUP(F2,A2:A9,B2:B9) (ID → name)
Pro9 minutes, 3 exercisesXLOOKUP

Watch it done

2 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]). You give it the column to search and the column to return as separate ranges, so it can look left as easily as right.

It matches exactly by default, and the fourth argument replaces #N/A with your own message. Inserting columns can't break it.

Syntax

=XLOOKUP(F2,A2:A9,B2:B9)ID → name
=XLOOKUP(F4,B2:B9,A2:A9)Name → ID (looks left)
=XLOOKUP(F6,A2:A9,C2:C9,"Not found")With a fallback

Watch out for

  • The lookup and return ranges must be the same height, or you get #VALUE!.
  • XLOOKUP needs Excel 2021 or Microsoft 365.

The exercises

  1. ID to name. In G2, return the name for the employee ID in F2.
  2. Look left. In G4, return the employee ID for the name in F4. VLOOKUP can't do this one.
  3. Handle missing IDs. In G6, return the department for the ID in F6, or the text Not found if the ID doesn't exist.

Questions people ask about this

Updated Checked by running: every exercise answer on this page was graded by our formula engine on .

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.

  1. ID to name
  2. Look left
  3. Handle missing IDs