Look Up Data by Using Advanced Functions

In data analytics, lookup functions are the Excel equivalent of a SQL LEFT JOIN. They allow you to search for a specific value in one dataset and return corresponding information from another column.

Understanding how these functions locate and retrieve data is crucial for the MO-211 exam and for building robust, error-free models in the real world.

Part 1: The Building Blocks (INDEX & MATCH)

Before looking at pre-packaged lookup formulas, it is best to understand the atomic functions that allow Excel to navigate a grid: INDEX and MATCH. Used independently, they are useful; used together, they create a highly flexible lookup engine.

MATCH(): Find the Position

• What it does: Finds the relative position (row or column number) of a lookup value. It does not return the data itself, just where it is located.
• Syntax: =MATCH(lookup_value, lookup_array, [match_type])
• Example: =MATCH("Inv-104", D:D, 0) -> Returns 5 (telling you the invoice is in row 5).
• Official Docs: MATCH function

INDEX(): Find the Value

• What it does: Returns the value of a cell in a specific row and column.
• Syntax: =INDEX(return_array, row_num)
• Example: =INDEX(A:A, 5) -> Returns the Customer Name located in cell A5.
• Official Docs: INDEX function

The INDEX/MATCH Combination

By nesting MATCH inside INDEX, you create a dynamic, two-way lookup. MATCH figures out what row the data is in, and hands that number to INDEX to retrieve the final value. * Combined Formula: =INDEX(A:A, MATCH("Inv-104", D:D, 0)) * Why it matters: Unlike other functions, INDEX/MATCH does not care about column order. It can look left, right, up, or down.

Part 2: The Classic Standard (VLOOKUP)

VLOOKUP (Vertical Lookup) is the most universally recognized lookup function in Excel and remains essential despite XLOOKUP's superiority. The syntax looks dense the first time you meet it, but it follows a fixed shape — value, table, column number, match type — that becomes second nature with a few examples.

VLOOKUP()

• How it works: It looks for a value in the left-most column of a table and returns a value in the same row from a column you specify.
• Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • Note: Always pass FALSE or 0 for the range_lookup argument to ensure an exact match. If omitted, VLOOKUP defaults to approximate match, which can silently return incorrect values when the lookup column is unsorted.
• Example: =VLOOKUP(A2, Customers!A:D, 4, FALSE) (Looks up the ID in A2 within the Customers sheet and returns the value in the 4th column).
• Official Docs: VLOOKUP function

The "Left-Lookup" Problem

VLOOKUP has one significant structural limitation: it only searches left-to-right. The lookup value must reside in the first column of the selected table_array.

If you need to look up an Invoice Number in Column D to return a Customer Name in Column A, VLOOKUP will fail. * The Quick & Dirty Fix: Move Column D before Column A. (Not recommended, as it alters source data). * The Professional Fix: Abandon VLOOKUP and use the INDEX/MATCH combo you learned in Part 1, which easily handles left-lookups.

Handling "Not Found" Errors

When VLOOKUP cannot find a match, it returns #N/A. To handle this gracefully in user-facing output, wrap the lookup in IFERROR. * The Solution: =IFERROR(VLOOKUP(A2, Customers!A:D, 4, FALSE), "Not Found")

Part 3: The Horizontal Sibling (HLOOKUP)

HLOOKUP()

• What it does: The exact same thing as VLOOKUP, but it searches horizontally. It looks for a value in the top row of a table and returns a value in the same column from a row you specify.
• When to use it: Rarely. It is only necessary when dealing with poorly structured, transposed data where records are stored in columns instead of rows.
• Official Docs: HLOOKUP function

Part 4: The Modern Evolution (XLOOKUP)

To solve the left-lookup limitations of VLOOKUP and the multi-formula complexity of INDEX/MATCH, Microsoft introduced XLOOKUP. It is faster, more powerful, and easier to write.

XLOOKUP()

• Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
• The Benefits:
  1. Searches Any Direction: Because you select the lookup column and return column separately, it easily performs left-lookups.
  2. Default Exact Match: Unlike VLOOKUP, XLOOKUP defaults to an exact match. You don't have to type 0 or FALSE at the end.
  3. Built-in Error Handling: The 4th argument handles #N/A errors directly, eliminating the need for IFERROR. Example: =XLOOKUP(A2, D:D, A:A, "Not Found").
  4. Immune to Column Insertions: If someone inserts a new column in the middle of your data, VLOOKUP breaks because its column index number is hardcoded. XLOOKUP uses dynamic arrays and will not break.
• Official Docs: XLOOKUP function

The Downside: Backwards Compatibility

XLOOKUP is only available in Microsoft 365 and Excel 2021 or newer.

If you author an XLOOKUP formula and send the workbook to someone running Excel 2016 or 2019, the formula resolves to #NAME? on their machine.

Best Practice: Use XLOOKUP for internal reporting on modern systems. If you are distributing files externally to unknown Excel versions, you must stick to VLOOKUP or INDEX/MATCH.

Part 5: Mastering the Full XLOOKUP Signature

Most users only ever touch the first three (or four) XLOOKUP arguments. The MO-211 exam is fond of the last two — match_mode and search_mode — because they unlock behaviors that previously required complex INDEX/MATCH constructions or sorted helper columns.

• Full Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Argument 4: if_not_found (Reinforcement)

• What it does: Replaces the #N/A error with text or a value of your choice when no match is found.
• When to use it: Always — for any user-facing report. It eliminates the need to wrap the whole formula in IFERROR.
• Example: =XLOOKUP(A2, Customers[ID], Customers[Name], "Unknown Customer")

Argument 5: match_mode

Controls how Excel compares the lookup value against the lookup array.

• Example (next smaller — find which discount tier a $4,200 order qualifies for): excel =XLOOKUP(4200, Tiers[Min_Spend], Tiers[Discount_Pct], 0, -1)
• Example (wildcard — find first customer whose name starts with "Acme"): excel =XLOOKUP("Acme*", Customers[Name], Customers[ID], "Not Found", 2)

Argument 6: search_mode

Controls the direction and algorithm Excel uses to scan the lookup array.

• Example (find the most recent transaction for a customer — assuming the log is in chronological order): excel =XLOOKUP("CUST-001", Log[Customer], Log[Amount], "No Activity", 0, -1)

Part 6: Wildcard Lookups in VLOOKUP and MATCH

For Excel 2019 and earlier (no XLOOKUP), wildcards still work in VLOOKUP and MATCH — but only when the match type is set to exact (FALSE / 0).

• * (asterisk) — matches any number of characters
• ? (question mark) — matches exactly one character
• ~ (tilde) — escapes a literal * or ? in your data

Wildcards with VLOOKUP

• Example (find a customer whose ID ends in "-2024"): excel =VLOOKUP("*-2024", Customers!A:D, 2, FALSE)
• Example (find a 5-character SKU starting with "AB"): excel =VLOOKUP("AB???", Products!A:C, 3, FALSE)

Wildcards with MATCH

• Example (return the row number of the first product containing "Pro"): excel =MATCH("*Pro*", Products[Name], 0)

• Combining cell references with wildcards — concatenate with &: excel =VLOOKUP("*" & A2 & "*", Customers!A:D, 2, FALSE)

Part 7: XMATCH — The Modern MATCH Replacement

XMATCH is to MATCH what XLOOKUP is to VLOOKUP: a modernized version that supports the same match_mode and search_mode arguments.

XMATCH()

• What it does: Returns the relative position of an item in an array, with full support for wildcard matching, reverse search, and binary search.
• When to use it: Whenever you would have used MATCH, but want wildcard support without sacrificing the speed of an exact match — or when you need to find the last occurrence rather than the first.
• Syntax: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

• Official Docs: XMATCH function

• Example (find the position of the last "Pending" status): excel =XMATCH("Pending", Orders[Status], 0, -1)

• Example (paired with INDEX for a left-lookup that works in any version supporting XMATCH): excel =INDEX(Customers[Name], XMATCH("Inv-104", Customers[Invoice], 0))

Part 8: True 2D Lookups with INDEX(MATCH, MATCH)

So far we've used MATCH to find a row. But INDEX also accepts a column number as its third argument. By nesting two MATCH calls — one for the row, one for the column — you can pinpoint any cell in a matrix using its row label and column label.

The Pattern

=INDEX(data_grid, MATCH(row_label, row_headers, 0), MATCH(col_label, col_headers, 0))

Example: Price-by-Product-and-Region Matrix

Imagine a pricing sheet where products are listed down column A, regions across row 1, and prices fill the grid B2:E10.

To find the price of Widget B in the East region:

=INDEX(B2:E10, MATCH("Widget B", A2:A10, 0), MATCH("East", B1:E1, 0))

• The first MATCH returns 2 (Widget B's row position).
• The second MATCH returns 3 (East's column position).
• INDEX then retrieves the cell at row 2, column 3 of the grid → 21.

Part 9: Approximate-Match Lookups for Tier Tables

Approximate match (the non-default mode for VLOOKUP and match_type=1 for MATCH) is the right tool for bracket-based lookups — commission tiers, tax brackets, shipping-weight pricing, performance grades.

How It Works

When set to approximate match, the function searches for an exact value first; if it can't find one, it returns the largest value that is still less than or equal to the lookup value.

Example: Commission Tiers

A salesperson with $32,000 in sales should land in the 6% tier.

• Using VLOOKUP (note TRUE for approximate match): excel =VLOOKUP(32000, Tiers!A2:B6, 2, TRUE)
• Using INDEX/MATCH with match_type 1: excel =INDEX(Tiers!B2:B6, MATCH(32000, Tiers!A2:A6, 1))
• Using XLOOKUP with match_mode -1: excel =XLOOKUP(32000, Tiers!A2:A6, Tiers!B2:B6, , -1)

Further reading

• VLOOKUP in Excel — Step-by-Step Tutorial for Beginners (YouTube) — a visual walkthrough of the classic VLOOKUP signature for learners who prefer video to prose.