Spreadsheet Practice

Lesson 10 of 29, Lookups

XLOOKUP

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

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.

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 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. ID to name
  2. Look left
  3. Handle missing IDs
Try these functions in the Formula Lab