Excel
All Top 50 Excel & Business Modeling Questions#7
Handling Missing Keys with XLOOKUP If-Not-Found Parameter
EasyAccentureInterview Question #7
Asked at AccentureYou have a list of User IDs in `A2:A50` and their registered Subscription Tiers in `B2:B50`. When querying for an ID entered in `D2`, write an XLOOKUP formula in `E2` that returns the subscription tier, or displays 'Unregistered User' if the ID does not exist.
Input Table: Users (A1:B4)
3 rows preview| UserID (Col A) | Tier (Col B) |
|---|---|
| USR-10 | Premium |
| USR-12 | Basic |
| USR-15 | Enterprise |
Expected Output Structure1 rows
| Query ID (D2) | Result (E2) |
|---|---|
| USR-99 | Unregistered User |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | UserID (Col A) | Tier (Col B) | ||||
| 2 | USR-10 | Premium | Target [Enter Formula] | |||
| 3 | USR-12 | Basic | ||||
| 4 | USR-15 | Enterprise | ||||
| 5 | ||||||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect