Skip to content
GridBelt
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign in
Start the free test
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign inCreate account
Start the free test
GridBelt

Practice Excel in your browser.

Excel Test

  • Level 1: Beginner
  • Level 2: Intermediate
  • Level 3: Expert
  • Excel test in 7 days
  • Leaderboard
  • Excel test for interview
  • Indeed Excel assessment

Practice

  • All exercises
  • Programs
  • For beginners

Guides

  • Excel formulas
  • Excel shortcuts

Company

  • About
  • Pricing
  • How we grade

© GridBelt. All rights reserved.

  • Privacy Policy
  • Terms of Use
  • Refund Policy

Excel is a trademark of Microsoft Corporation. Indeed, TestGorilla, SHL and other marks are the property of their owners. GridBelt is an independent practice site and is not affiliated with, endorsed by or sponsored by Microsoft, TestGorilla, SHL or Indeed.

  1. Practice
  2. /
  3. XLOOKUP

XLOOKUP look-left practice: employee ID by email

  • Expert
  • ~6 min
  • XLOOKUP
  • Excel 365

IT received 90 system-access requests in November 2026; the request form captured only the requester's email (column C of the Requests sheet). The HR Directory sheet lists 90 employees with the employee ID in column A and the email in column D — the ID sits to the left of the key, which VLOOKUP cannot return. A few emails were typed with capital letters; Excel lookups ignore case, so they still match. Fill column E with the employee ID for every request.

What you need to do

  1. 1On Requests, click E2 and type =XLOOKUP(C2, — the email is the value to find.
  2. 2Click the Directory tab, select the Email column D2:D91 and press F4 (Windows) or ⌘T (Mac) to lock it; type a comma.
  3. 3Still on Directory, select the Employee ID column A2:A91, lock it, close the bracket and press Enter — the return column can be anywhere, including left of the lookup column.
  4. 4Fill E2 down to E91. Spot-check one of the capitalised emails: it returns the same ID as its lower-case twin.

Answer cells

Feedback

Type your formula in the grid, press Enter, then Check. We grade the value in each answer cell and the formula behind it.

Tasks

  1. Not checkedOn the Requests sheet, in E2, use XLOOKUP to return the employee ID (Directory column A) for the email in C2, matching against the Email column of the Directory sheet, then fill down to E91.

Desktop recommended for spreadsheet tasks. The sheet scrolls inside this box.

StartInsertFormulasDataView
A1fxPreparing your workbook…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Enter your formula in the sheet, then check.

About this exercise

A 180-row dataset on 2 sheets, one answer cell, about 6 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.

How to solve it

  • Requests!E2=XLOOKUP(C2,Directory!$D$2:$D$91,Directory!$A$2:$A$91)

XLOOKUP takes the column to search and the column to return as two separate ranges, so their relative position does not matter: here the search column (Email, D) is to the right of the return column (Employee ID, A). VLOOKUP cannot do this, because it only returns columns to the right of its first column. Text matching in XLOOKUP, like in all Excel lookups, ignores case, which is why "Maya.Patel@…" finds "maya.patel@…".

Pitfall 1: lookups ignore case but not spaces — an email pasted with a trailing space returns #N/A; wrap the lookup value in TRIM when the source is a web form. Pitfall 2: emails are good keys only while they are unique; a shared mailbox used by two people returns the first match silently. Pitfall 3: the pre-365 work-arounds all work in this engine except one — INDEX/MATCH =INDEX(Directory!$A$2:$A$91,MATCH(C2,Directory!$D$2:$D$91,0)) and the array trick =LOOKUP(2,1/(Directory!$D$2:$D$91=C2),Directory!$A$2:$A$91) compute correctly, while the VLOOKUP-with-CHOOSE({1,2},…) trick does not evaluate in this browser engine (it does in desktop Excel). Alternative in 365: =XLOOKUP(C2,Directory!$D$2:$D$91,Directory!$A$2:$A$91,"Unknown email") adds a readable fallback. Design note: looking left is the most common reason teams finally abandon VLOOKUP — reordering the source table to put the key first is not an option when the sheet is owned by HR.

Other ways that work

  • =XLOOKUP(C2,Directory!$D$2:$D$91,Directory!$A$2:$A$91,"Unknown email") — with a fallback for emails that are not in the directory
  • =INDEX(Directory!$A$2:$A$91,MATCH(C2,Directory!$D$2:$D$91,0)) — the version-independent form (not accepted here: this task practices XLOOKUP)

Common mistakes

Trying =VLOOKUP(C2,Directory!$D$2:$D$91,-3,FALSE) or a table starting at D: VLOOKUP cannot count backwards, so the result is #VALUE! or #N/A. With XLOOKUP you simply pass the Employee ID column as the return array.

How we grade

Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.

Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.

In these programs

  • Excel for Data AnalystsDay 9

Related exercises

  • Replace VLOOKUP with XLOOKUPIntermediate, ~6 min
  • Two-criteria lookup (product and region)Expert, ~10 min
  • INDEX and MATCH exact lookupIntermediate, ~6 min
  • Look up a customer tier from another sheetBeginner, ~5 min

Function reference

Official documentation

Next exercise

Two-criteria lookup (product and region)