Sample tutorial: XLOOKUP

Template tutorial

This page is the template I’ll use for future Excel lessons. The structure — goal → setup → walk-through → pitfalls → cheat-sheet — is the shape every tutorial on this site will roughly follow.

Goal

By the end of this five-minute lesson, you’ll be able to use XLOOKUP to pull a value from one table into another, handle missing matches gracefully, and know why your instructor stopped using VLOOKUP two years ago.

You’ll need

• Excel for Microsoft 365, Excel 2021, or newer (XLOOKUP isn’t in 2019).
• A workbook with two ranges to join — e.g. a list of student IDs and a separate roster with names. The download link (placeholder) will live here in real lessons.

Walk-through

1. The basic shape

XLOOKUP answers the question “given this value, give me the matching value from another column.”

=XLOOKUP(lookup_value, lookup_array, return_array)

So if A2 holds a student ID and the roster lives in Roster!A:B (IDs in column A, names in column B):

=XLOOKUP(A2, Roster!A:A, Roster!B:B)

That’s it. No “fourth argument that’s almost always 0”, no counting columns, no nervous glance at whether the lookup column is on the left.

2. Handle missing matches

Real data has missing rows. The fourth argument lets you say what to return when nothing matches — instead of #N/A:

=XLOOKUP(A2, Roster!A:A, Roster!B:B, "Not on roster")

3. Look left, look right, doesn’t matter

VLOOKUP could only look to the right of the key column. XLOOKUP doesn’t care — the lookup array and return array are independent, so you can fetch a value from a column to the left of your match column with no gymnastics.

4. Return more than one column

Pass a multi-column range as return_array and (in modern Excel) the result spills sideways:

=XLOOKUP(A2, Roster!A:A, Roster!B:D)

You get the matched name, email, and major in one call — three formulas collapsed into one.

Common pitfalls

Watch out for these

• Approximate match by accident. The fifth argument controls match mode. Leave it off for an exact match; pass 1 only when you genuinely want the next-larger item.
• Whole-column ranges in big workbooks. Roster!A:A is convenient but slow on large files. Convert your roster to a Table and reference it as Roster[ID] — faster and it auto-expands when you add rows.
• Spilled results into a non-empty cell. If the cell below your formula already has content, you’ll see #SPILL!. Clear the destination range or move the formula.

Cheat-sheet

You want to…	Formula
Look up a name by ID	=XLOOKUP(A2, Roster[ID], Roster[Name])
Show a friendly message when missing	=XLOOKUP(A2, Roster[ID], Roster[Name], "Not on roster")
Return several columns at once	=XLOOKUP(A2, Roster[ID], Roster[[Name]:[Major]])
Find the last match instead of the first	=XLOOKUP(A2, Roster[ID], Roster[Name], , 0, -1)

Try it yourself

1. Open the practice workbook (link will go here).
2. On the Grades sheet, add a column called Student name.
3. Use XLOOKUP against the Roster table to populate it.
4. Add a fourth argument so missing IDs show "Not on roster" instead of #N/A.

Take-aways

• XLOOKUP replaces VLOOKUP, HLOOKUP, and most INDEX/MATCH patterns with one consistent shape.
• Always pass the if-not-found argument — #N/A in a deliverable is a bug, not a feature.
• Reference Tables, not whole columns, in any workbook you’d hand to someone else.