Confident Cells

XLOOKUP: a simple worked example

20 November 2026 · 2 min read

XLOOKUP looks for a value in one column, then brings back the value from the same row of another column. It does the job of VLOOKUP without counting columns, and the column it brings back can be on either side.

A worked example

Here's a staff list. Someone asks for Lucy's staff ID.

ABC
1Staff IDEmployeeTeam
21001GraceSales
31002TomFinance
41003PriyaSupport
51004LucySupport
61005MarekSales

By hand you would:

  1. Go down the Employee column until you find Lucy. She's the 4th name.
  2. Take the 4th value from the Staff ID column: 1004.

The ID is to the left of the name, which VLOOKUP can't do. XLOOKUP doesn't mind, because you give it the column to search and the column to bring back separately.

The formula

=XLOOKUP(F1,B2:B6,A2:A6)
ABCDEF
1Staff IDEmployeeTeamNameLucy
21001GraceSalesStaff ID1004
31002TomFinance
41003PriyaSupport
51004LucySupport
61005MarekSales
F2 holds =XLOOKUP(F1,B2:B6,A2:A6).

XLOOKUP matches exactly unless you ask it not to, so there's no FALSE to remember.

Showing a message when there's no match

If the name isn't on the list, XLOOKUP shows #N/A. Add a fourth part to show something friendlier:

=XLOOKUP(F1,B2:B6,A2:A6,"Not found")

XLOOKUP or VLOOKUP?

Common mistakes

#VALUE!: the two columns are different sizes

B2:B6 and A2:A7 don't line up, so XLOOKUP refuses. Make both ranges start and end on the same rows.

#NAME?: your Excel doesn't have XLOOKUP

Excel 2019 and earlier don't recognise the name. Use INDEX and MATCH, which do the same job.

#N/A when the value looks right

Check for a space at the end of the name, or a number stored as text. TRIM removes stray spaces.

Ranges that move when you copy down

If you copy the formula down a column, lock both ranges with $: =XLOOKUP(E2,$B$2:$B$6,$A$2:$A$6). Press F4 to add the dollar signs.

Try it now

Your turn

In F2, find the staff ID of the employee named in F1. The IDs are to the left of the names.

ABCDEF
1Staff IDEmployeeTeamNameLucy
21001GraceSalesStaff ID
31002TomFinance
41003PriyaSupport
51004MarekMarketing
61005NiaSales
71006OllieFinance
81007LucySupport
91008HamzaMarketing

Where it works

Excel for Microsoft 365, Excel 2021 onwards, Excel for the web, Google Sheets and LibreOffice Calc 24.8 onwards.