cswork / libraryOpen practice workspace

Articles

Excel Lookup Interview Questions: XLOOKUP, Duplicate Keys, and Silent Mismatches

Practice XLOOKUP with duplicate SKU rows, exact-match defaults, text-number key errors, and a zero-one-many audit before trusting prices.

An Excel price sheet contains two rows for SKU A-7: $12 and $15. XLOOKUP returns $12 without an error. A manager calls the result “accurate because the formula worked.” In an interview, the useful answer is not to recite the function arguments. It is to ask whether A-7 is supposed to have one current price, whether the rows are time-versioned, and what evidence would prove that $12 is the right one.

Microsoft's XLOOKUP reference says exact match and first-to-last search are defaults. A successful first match therefore proves only that at least one matching key was found. The price sheet below is an original practice fixture, not a candidate report or a claim about a particular employer's test. No verified same-cycle candidate account establishes this exact prompt. For relational-cardinality preparation, Illustrate SQL Join Results with Duplicate Keys makes the underlying key problem explicit.

Question 1: What does XLOOKUP return when keys repeat?

Suppose the source is an Excel Table named Prices. These values are invented. effective_at is a business timestamp; row order is shown as it happens to appear in the sheet, not a guarantee of chronology.

Sheet rowSKUPriceeffective_at
2A-7122026-08-01
3A-892026-09-01
4A-7152026-09-01

=XLOOKUP("A-7",Prices[SKU],Prices[Price]) returns 12 under the documented default first-to-last search. It does not reject the duplicate key. =XLOOKUP("A-7",Prices[SKU],Prices[Price],"Missing",0,-1) searches backward and returns 15 in this specific row order. But “last sheet row” is not the same as “latest effective price” if someone sorts the Table differently or imports a late historical correction. Reverse search is a positional rule. To select the newest valid price, sort or filter by an explicit effective-time and tie-break policy, then validate that the result still meets the rule.

The official syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The fifth argument defaults to exact match (0), and the sixth defaults to forward search (1). State which optional argument you are changing: -1 in the sixth slot means reverse search, while -1 in the fifth slot means exact or next-smaller approximate match. Confusing them can produce a plausible but wrong price rather than an obvious formula error.

If A-7 legitimately has two historical prices, the key is incomplete for a time-aware lookup. A request for the price “on September 3” needs SKU plus an effective-date rule, perhaps the latest effective time not after the as-of date. XLOOKUP on SKU alone cannot express that full contract. A request for the current price could instead use a maintained one-row-per-SKU current-price table. The right design depends on the business question; the function's first-match behavior does not define it.

Question 2: How would you audit zero, one, and multiple matches?

Create a match-count column before trusting the returned value. For a SKU in G2, =COUNTIF(Prices[SKU],G2) is a useful first pass. Microsoft's worksheet-count guidance confirms that COUNTIF counts occurrences meeting a criterion. In the fixture, A-7 has count 2, A-8 has count 1, and an absent A-9 has count 0. Turn those into three review buckets: missing, unique, and multiple. Do not display one number for all three conditions without a quality signal.

Lookup keyMatch countNaive XLOOKUPAudit interpretation
A-7212Ambiguous price unless a survivor rule is declared
A-819Unique match in this snapshot
A-90#N/A or configured fallbackMissing reference, not a zero price

COUNTIF is an audit aid, not an absolute proof of canonical identity. It can apply criteria semantics that may not match every domain's desired exactness, especially when wildcards, case, or normalization matter. If SKU identity is case-sensitive, compare candidates with a deliberate rule such as Excel's case-sensitive EXACT function and check the source system's key contract. If a SKU contains * or ?, inspect whether a criteria-based count interprets those characters specially. The core logic remains: determine the number and meaning of candidate rows before selecting one.

For a production workbook, put the exception count near the output: number of zero-match keys, number of multi-match keys, and number of rows where returned price differs from an independently approved price list. Inspect exceptions individually. The audit does not need to make every anomaly disappear automatically. An unresolved duplicate should usually block a price update or be labeled for review, because an arbitrary choice can affect revenue or customer quotes.

Question 3: Why can an apparently identical key fail to match?

Imagine an order sheet with SKU 0042 as text. The price reference has numeric 42 because an import removed leading zeroes. These are not interchangeable product identifiers even if a human thinks they refer to the same product. Microsoft warns in its number-as-text guidance that leading zeroes may be lost when data is treated numerically and that imported numeric text can cause confusing operations. Before coercing both sides to numbers, ask whether 0042 and 42 are meant to be distinct SKUs. If they are identifiers, normalize to the agreed text representation at import and preserve leading zeroes.

Other near matches include A-7 versus A-7 with a trailing space, a hidden nonbreaking space, or different dash characters. A formula returning #N/A may be accurately reporting no exact key match. The VLOOKUP troubleshooting guidance identifies number-as-text and stray characters as common lookup problems; use that as diagnostic context, while relying on the XLOOKUP documentation for XLOOKUP's own argument behavior. Compare lengths and character codes or show the value in a controlled helper column before trimming. Do not modify identifiers blindly if spaces or punctuation have meaning in the source system.

Silent mismatch can also occur when a value does match but the wrong row wins. A-7 returning 12 is more dangerous than #N/A, because it looks complete. That is why a data-validation check should distinguish “found a row” from “found the uniquely authorized row.” A clean formula can sit on bad source data. Explain both layers in the interview.

If asked about letter case, avoid guessing. Microsoft documents EXACT as case-sensitive; if case matters to the business key, test it directly rather than assuming a default lookup meets that policy. If case should not matter, normalize both source and request under a documented rule and still verify duplicate counts after normalization. Two previously distinct keys may collapse into one ambiguous key.

Question 4: When are approximate or binary modes dangerous?

XLOOKUP's default is exact match. You can choose approximate options for threshold tables, such as tax brackets or volume bands, but that changes the meaning of a missing key. If SKU A-9 is absent, “next smaller” might return A-8's price in an ordered list, which would be unacceptable for exact product pricing. State match_mode=0 explicitly in a shared workbook when it makes the intent easier to audit, even though it is the default.

The XLOOKUP reference also provides binary search modes 2 and -2. They rely on the lookup array being sorted ascending or descending respectively; Microsoft warns that unsorted data can return invalid results. The three-row fixture is intentionally not sorted by SKU because A-7 appears on both sides of A-8. Do not switch to binary mode merely because the sheet is large. Verify the sort invariant, duplicate policy, and required match semantics first. A fast wrong lookup is still wrong.

An interview follow-up may ask whether a search_mode=-1 reverse lookup solves duplicate prices. It only picks the last matching row in the current sequence. If the Table is sorted by effective_at ascending and every SKU's records obey that rule, reverse search can be part of a current-price strategy, but you still need a cutoff, tie-breaker, and verification that rows were not reordered. Often a dedicated current-price reference is easier to maintain. Say what must remain true for your chosen formula to remain correct.

Question 5: How should missing matches be presented?

The optional [if_not_found] controls the display when no valid match exists. By default, XLOOKUP returns #N/A. =XLOOKUP(G2,Prices[SKU],Prices[Price],"Missing",0) can make a review sheet readable, but “Missing” is not a price. Do not use 0 as a universal fallback in a price column: zero could be a legitimate free item or could understate revenue when a SKU is absent. A separate status field makes exceptions visible to the person approving the workbook.

Avoid a broad IFERROR wrapper that turns wrong ranges, type issues, and other formula errors into a harmless-looking blank. It erases information needed to repair the workbook. If the desired display is blank for missing SKUs, keep an independent missing-key counter and make the downstream calculation reject or flag those rows. For a reconciliation, add up order values only after confirming that every included SKU has the intended price and no unresolved duplicates. A total can match by coincidence even when individual rows are wrong.

Check range alignment too. lookup_array and return_array should describe corresponding rows. If one structured reference points to an old table and the other to a newly sorted copy, the formula can return values from unrelated records. A Table with both key and price columns keeps references together, but a source refresh can still alter meaning. Test known keys at the top, middle, and bottom of the table and compare them to the authoritative record.

A defensible interview answer

“XLOOKUP defaults to exact match and returns the first match, so A-7 returns $12 in the current sheet. That is a successful lookup, not proof of the correct current price. I would count matches for each requested SKU and route zero and multiple matches to review. If the business wants the latest price, I would define an effective-date and tie-break rule or maintain a one-current-row reference. I would also verify text-versus-number keys and spaces before normalizing them, because identifiers can carry leading zeroes. I would keep missing matches visible rather than silently substituting zero.”

Then challenge your own answer. Sort the source rows differently: does the chosen price change? Add an A-7 row with the same effective date: is there a stable tie-breaker? Insert SKU 0042 as text and 42 as a number: does the normalization policy preserve identity? Remove A-8: does the report signal a missing reference? These four tests are more persuasive than a screenshot of one successful formula.

The scope boundary is worth stating: this is an Excel lookup exercise, not an authoritative pricing-engine design. For a high-stakes operational system, price validity, approvals, effective dates, and audit logs belong in governed source data. The workbook should consume and verify that contract. XLOOKUP is a retrieval function; governance is a separate decision.

Sources and further reading