Lookups Xlookup Index Match

Why this matters

Marketing Analysts constantly stitch data from multiple sources: ad platforms, web analytics, and CRM. Lookups let you join those sources fast to answer questions like:

• Map campaign IDs to readable names and owners.
• Attach costs to conversions by day and channel.
• Fill missing attributes (country, product line) from a master table.
• Create rules-based tiers (e.g., spend brackets, CPC thresholds).

Concept explained simply

Lookups find a value in one table and return a related value from the same row. Think of it as performing a simple left join in a spreadsheet.

Mental model

• Key: the thing you know (e.g., Campaign_ID)
• Lookup table: where the key lives (e.g., a dimension sheet)
• Return column: what you want back (e.g., Campaign_Name)
• Match rule: exact match vs. closest match (for tiers/bands)

Syntax essentials

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- match_mode: 0 exact (default), -1 next smaller, 1 next larger, 2 wildcard
- search_mode: 1 first-to-last (default), -1 last-to-first

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
- MATCH type 0 = exact match
- Two-criteria pattern: MATCH(1, (range1=val1)*(range2=val2), 0) entered as array-enabled (modern Excel/Sheets auto-handle)

Worked examples

1) Basic ID → Name with XLOOKUP

Task: From Ad_Report!A:A (Campaign_ID), return Dim_Campaigns!B:B (Campaign_Name).

Formula in Ad_Report!B2:
=XLOOKUP(A2, Dim_Campaigns!$A:$A, Dim_Campaigns!$B:$B, "Not found")

Why: Simple one-key join; custom message keeps your sheet clean.

2) "Left" lookup (return column left of key)

Task: Return Campaign_Owner from a column to the LEFT of Campaign_ID.

INDEX/MATCH in Ad_Report!C2:
=INDEX(Dim_Campaigns!$C:$C, MATCH(A2, Dim_Campaigns!$A:$A, 0))

Tip: XLOOKUP can also do this without direction limits.

XLOOKUP alternative:
=XLOOKUP(A2, Dim_Campaigns!$A:$A, Dim_Campaigns!$C:$C, "Not found")

3) Two-criteria: Campaign_ID + Date → Spend

Task: From Fact_Spend table (columns: Campaign_ID, Date, Spend), get daily spend.

INDEX/MATCH (two criteria) in Report!D2:
=INDEX(Fact_Spend!$C:$C, MATCH(1, (Fact_Spend!$A:$A=$A2)*(Fact_Spend!$B:$B=$B2), 0))

Sheets tip (or Excel 365): You can also use a helper key:

Helper in Fact_Spend!D: =Fact_Spend!A2 & "|" & Fact_Spend!B2
Then XLOOKUP in Report!D2:
=XLOOKUP($A2 & "|" & $B2, Fact_Spend!$D:$D, Fact_Spend!$C:$C, "Not found")

4) Approximate match: CPC tiering

Task: Assign a tier based on CPC thresholds in a small table:

Thresholds: 0 Tier A
0.5 Tier B
1 Tier C

XLOOKUP with next smaller match in Report!E2:
=XLOOKUP(C2, Thresholds!$A:$A, Thresholds!$B:$B, "No tier", -1)

Meaning: For CPC in C2, return the tier for the largest threshold less than or equal to CPC.

Data hygiene tips

• Trim spaces: wrap keys with TRIM and CLEAN if pasted from exports.
• Consistent types: ensure IDs are all text or all numbers on both sides.
• Unique keys: duplicates can return unexpected rows; deduplicate or specify rule.
• Stability: avoid whole-column return ranges if performance is slow; use sized ranges.

Exercises (practice)

Do these in any spreadsheet app. Mirror of the Exercises section below. Use the checklist to self-verify.

• Exercise 1: XLOOKUP ID → Name with not-found handling.
• Exercise 2: Two-criteria lookup (Campaign_ID + Date) to get Spend.
• Exercise 3: Approximate match for CPC tiers.

• Columns used are locked to absolute references ($) where needed.
• Your formulas return the correct values even after inserting a new column in the lookup table.
• Error cases show a friendly message instead of #N/A.

Common mistakes and self-check

• Mixing text and numbers for keys. Self-check: COUNT of mismatches after coercing both with VALUE or TEXT shows zero.
• Using approximate match accidentally. Self-check: Confirm match_mode=0 (XLOOKUP) or MATCH type 0.
• Hardcoding column indexes (VLOOKUP). Self-check: Prefer XLOOKUP or INDEX/MATCH referencing named columns.
• Hidden spaces. Self-check: Test =LEN(A2) and compare; use TRIM.
• Duplicates in key. Self-check: COUNTIF(key_range, key) should be 1 for exact joins.

Practical projects

• Build a Campaign Performance Hub: Join ad spend (daily) to CRM conversions by Campaign_ID and Date; add owner and channel attributes; flag missing joins.
• UTM Cleaner: Use lookup tables to normalize medium/source names and map to channels.
• Tiered Budget Rules: Assign spend tiers to campaigns using CPC/CPA thresholds with approximate matches; highlight campaigns in the top tier.

Who this is for

• Marketing Analysts and growth professionals who join data from multiple exports.
• Anyone moving from VLOOKUP to modern, safer lookup patterns.

Prerequisites

• Basic spreadsheet navigation and formulas.
• Comfort with cell references and ranges.
• Optional: Named ranges for readability.

Learning path

1. Exact lookups with XLOOKUP.
2. Left/right agnostic lookups.
3. Two-criteria lookups with INDEX/MATCH or helper keys.
4. Approximate matches for tiers/bands.
5. Error handling and data hygiene.

Mini challenge

You have a table of leads with Campaign_ID and Date. Create two columns: Owner (from Dim_Campaigns) and Cost (from Fact_Spend). If an Owner is missing, show "Owner TBD"; if Cost is missing, show 0. Confirm your formulas still work after inserting a new column into Dim_Campaigns.

Next steps

• Convert key ranges to named ranges for clarity.
• Add data validation to prevent invalid keys.
• Practice on a fresh export weekly to build speed.

Quick Test

This test is available to everyone. Only logged-in users will see their progress saved.