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.