Look up attributes without hiding exceptions — Smart Study Module 3

Each payment needs its branch, but a wrong match would send collection credit to the wrong team.

Outcomes

• Select exact match deliberately
• Detect duplicate keys before lookup
• Use empty spill space for arrays

Understand

XLOOKUP uses exact matching by default and returns the first match unless search mode is changed. That does not prove the key is unique. Validate Loans[loan_id] before joining attributes.

Use a visible Missing loan result for unknown IDs and review those rows. Do not silently substitute a branch. INDEX/MATCH with match_type 0 is an older-version exact-match alternative.

FILTER and SEQUENCE require a compatible modern Excel edition and sufficient empty spill space. A blocked spill range is a layout error, not evidence that no matching rows exist. Browser support is partial.

Follow along

1. Validate four unique loan keys.
2. Add branch_id to Payments with an exact lookup.
3. Temporarily test L999 and inspect the explicit missing result.
4. Restore the source and check that P005 maps to B02.

Worked example

=XLOOKUP([@loan_id],Loans[loan_id],Loans[branch_id],"Missing loan",0)
=INDEX(Loans[branch_id],MATCH([@loan_id],Loans[loan_id],0))

L004 maps to B02 (Nashik); L999 is Missing loan.

Practice

Which branch_id does L004 map to? Enter its exact ID.

Create a free personalized study plan · Download project instructions

Interactive practice runs in your browser.