Hands-On Excel

Hands-On Excel Challenges XLOOKUP

Excel XLOOKUP practice

=XLOOKUP(E2, $B$2:$B$7, $A$2:$A$7, "Not found") finds E2 in column B and returns the value from the same row of column A. The 4th argument is what to show when there is no match.

CoreAbout 3 minutesFree, no sign-upBy Updated

Try it yourself

12 · XLOOKUP · Core

XLOOKUP, the modern way

Find each person's phone extension. In F2:F4, look up the name from column E in the staff list and return the extension from column A. If a name isn't on the list, show "Not found".

XLOOKUP(what, where to look, what to return, if not found). The column you return can be to the left of the one you search, which VLOOKUP can't do.

staff.xlsx
Ready

Write in the green cells, then press Check. Click a cell while typing to insert it, drag to select a range, F4 adds $, Esc cancels.

The guide · XLOOKUP

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Why it's easier than VLOOKUP

XLOOKUP is in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. Older versions show #NAME?, so for files shared with them use VLOOKUP or INDEX and MATCH.

The two ranges must line up

XLOOKUP finds the position of the match in the lookup range and returns the item at the same position in the return range. If one range starts on row 2 and the other on row 1, every answer comes from the row above: no error, just wrong numbers. Make both ranges start and end on the same rows.

Common mistakes

FAQ

What is the difference between XLOOKUP and VLOOKUP?
XLOOKUP takes a lookup column and a return column instead of a table and a column number. It can return values to the left, matches exactly by default and has a built-in if_not_found argument.
Which versions of Excel have XLOOKUP?
Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. Excel 2019 and older do not have it.
How do I stop XLOOKUP returning #N/A?
Give it a 4th argument: =XLOOKUP(E2,B2:B7,A2:A7,"Not found") shows Not found when there is no match.
Can XLOOKUP look to the left?
Yes. The return range can be any column, including one to the left of the column you search.

All challenges

  1. 01 Add it upSUM Starter · 3 min
  2. 02 Find the averageAVERAGE Starter · 3 min
  3. 03 Write once, fill downRelative references Starter · 3 min
  4. 04 Lock it with $Absolute references Core · 3 min
  5. 05 Percent of a totalPercentages Starter · 3 min
  6. 06 Select to the end in one moveCtrl + Shift + arrow Core · 3 min
  7. 07 Pass or fail?IF Core · 3 min
  8. 08 Count the ones that matchCOUNTIF Core · 3 min
  9. 09 Add up with two conditionsSUMIFS Core · 3 min
  10. 10 Look it upVLOOKUP Core · 3 min
  11. 11 Find the right bracketVLOOKUP approximate match Core · 3 min
  12. 12 XLOOKUP, the modern wayXLOOKUP Core · 3 min
  13. 13 INDEX and MATCH togetherINDEX + MATCH Advanced · 3 min
  14. 14 Catch the error, not the bugIFERROR Core · 3 min
  15. 15 Split first and last namesLEFT, MID + FIND Core · 3 min
  16. 16 Mistake hunt: #N/A everywhereFixing #N/A Core · 3 min

Pro · coming soon

From first formula to job-ready.

100+ challenges on lookups, pivot tables, dates and text cleanup, an interview track modeled on the Excel tests employers use, and your progress saved across devices.

$29 one-time · early-bird price

You won't be charged today.

You're early!

Pro isn't open yet. We're building it right now. Leave your email and we'll hold the $29 early-bird price for you and tell you the day it launches.

We'll email you a link to confirm, then one email when Pro launches. Privacy