Enrich an order with its segment
Return the segment of the customer on order O-4, or Unmapped if the customer is missing from the segment table.
Formula: =XLOOKUP(B5,E2:E4,F2:F4,"Unmapped") → Retail
Hands-on Excel exercises
Excel Skills for Data Analysts
Practice Excel for data analysts: distinct counts with UNIQUE, enriching orders with XLOOKUP, FILTER sums, and COUNTIFS checks, with answers.
A data analyst's first spreadsheet question is rarely the answer itself. It is what one row represents, how many distinct things are in the table, and whether a join to reference data lost anything. Screening tests for analyst roles reflect that: they hand you an orders list and a customer table, and ask for distinct counts, enriched columns, and filtered totals.
The live sheet has seven orders and a separate customer-segment table. Your task is to count distinct customers using UNIQUE inside COUNTA, a pattern that works only in Excel versions with dynamic arrays. The worked answers then enrich an order with its segment using XLOOKUP and an explicit unmapped label, total the amounts for one customer with FILTER, and count large orders for another with COUNTIFS. Between them they cover profiling, joining, and aggregating.
Build the habit of checking row counts before and after every step. If a lookup returns Unmapped, that is a finding, not a nuisance. If two methods give different totals, one of the ranges is wrong. Record the question you answered, not just the number. For modeling-heavy roles, the financial analysts page uses the same ideas on cash flows; for tidy-up tasks, the assessment page covers cleaning.
Your turn
Your turn: in G2, count the distinct customers in the Customer column of the orders (B2:B8).
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Customer | Amount | Customer | Segment | |||
| 2 | O-1 | C1 | 120 | C1 | Retail | |||
| 3 | O-2 | C2 | 80 | C2 | Wholesale | |||
| 4 | O-3 | C1 | 200 | C3 | Retail | |||
| 5 | O-4 | C3 | 45 | |||||
| 6 | O-5 | C2 | 310 | |||||
| 7 | O-6 | C1 | 95 | |||||
| 8 | O-7 | C4 | 60 | |||||
| 9 |
Not yet solved.
Answer: =COUNTA(UNIQUE(B2:B8))
Practice under a time limit
Try the free Sample mock exam with 4 tasks in 8 minutes, or browse the level and interview mock exams included with Pro + Exams.
Related exercises
Open a task in its lesson.
- In G7, add up G2:G6. Now SUM sees the numbers.
- In G2:G5, look up the price, and show 0 for an unknown SKU.
- In F5, find Daniel's largest deal.
- In F6, find the weakest result with the SMALL function.
- Build a cross-tab PivotTable in I1: destinations in rows, tour types in columns, the number of sales.
- In F9:F11, calculate the revenue of each city for the chosen month.
Worked answers
Try the spreadsheet first, then compare your formula and result.
Find an unmapped customer
Return the segment of the customer on order O-7, or Unmapped if it is missing from the segment table.
Formula: =XLOOKUP(B8,E2:E4,F2:F4,"Unmapped") → Unmapped
Total one customer with FILTER
Add up the amounts of all orders for customer C1.
Formula: =SUM(FILTER(C2:C8,B2:B8="C1")) → 415
Count large orders for one customer
How many orders for customer C2 are above 100?
Formula: =COUNTIFS(B2:B8,"C2",C2:C8,">100") → 1
Frequently asked questions
What if my Excel does not have UNIQUE?
Older versions lack dynamic arrays. A distinct count can be built with SUMPRODUCT and COUNTIF, or with a PivotTable.
What should I do with an unmapped key?
Report it. An unmapped customer means the reference table is incomplete, and the count of unmapped rows is part of your findings.
Why check row counts?
A join on a repeated key can multiply rows and silently inflate totals. Compare counts and totals before and after.
When is FILTER better than SUMIFS?
SUMIFS is simpler for sums and counts. FILTER is useful when you need the rows themselves or a complex condition.