Training By Himanshu

Tag: Excel Lookup

  • Master the XMATCH Function in Excel 365

    📘 What is the XMATCH Function in Excel?

    The XMATCH function is a modern alternative to MATCH, introduced in Excel 365 and Excel 2021. It returns the relative position of an item in a row or column. Unlike MATCH, it supports reverse search, wildcard matching, and exact or approximate search modes.

    🔧 Syntax of XMATCH

    =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
    ParameterDescription
    lookup_valueThe value you want to search for
    lookup_arrayThe range or array to search in
    match_mode(Optional) 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match
    search_mode(Optional) 1 = first-to-last (default), -1 = last-to-first, 2 = binary ascending, -2 = binary descending

    ✅ Key Features of XMATCH

    • Supports vertical and horizontal lookups
    • Can search from last to first (search_mode = -1)
    • Allows wildcard characters (match_mode = 2)
    • Works with arrays and spilled ranges
    • Better compatibility with dynamic arrays

    🧪 Examples

    🔹 Example 1: Basic Exact Match

    =XMATCH("Priya", A2:A10)

    Searches for “Priya” in the list and returns the position where it’s found.

    ✅ If “Priya” is in cell A5 (4th position in A2:A10), the result is 4.

    🔹 Example 2: Wildcard Match

    =XMATCH("P*", A2:A10, 2)

    Returns the first item starting with “P”.

    ✅ Useful for partial string lookups.

    🔹 Example 3: Reverse Search

    =XMATCH("Complete", A2:A10, 0, -1)

    Searches bottom-up for “Complete”.

    🔹 Example 4: Approximate Match

    If you have numbers like 50, 60, 70, and you’re looking for 65:

    =XMATCH(65, A2:A10, 1)

    Returns the position of the next larger number (70).

    🔹 Example 5: Use with INDEX for Advanced Lookup

    =INDEX(B2:B10, XMATCH("Ravi", A2:A10))

    Finds Ravi in column A and returns corresponding value from column B.

    ✅ Powerful alternative to VLOOKUP or INDEX+MATCH.

    🎓 Common Use Cases

    • Find row/column numbers dynamically
    • Combine with INDEX for 2D lookups
    • Reverse search to find last matching item
    • Match using wildcards like "*Report" or "Jan???"
    • Create dynamic dashboards or filters

    ❓ 5 Interview-Based Questions on XMATCH

    1. What is the key difference between XMATCH and MATCH in Excel? (Expected: XMATCH supports reverse search, wildcards, exact/approximate modes, and works with dynamic arrays.)
    2. How would you find the last occurrence of a value in a list using XMATCH? (Hint: Use search_mode = -1)
    3. What does the following formula return? =XMATCH(75, A2:A6, -1) (Expected: Returns the position of the largest number less than or equal to 75.)
    4. Can XMATCH be used with INDEX to replicate VLOOKUP? Provide an example. (Yes, e.g., =INDEX(B2:B10, XMATCH("ItemName", A2:A10)))
    5. Explain how to use XMATCH for partial matches using wildcards. (Set match_mode = 2, e.g., =XMATCH("Jan*", A2:A10, 2))

    📌 Final Thoughts

    XMATCH is more powerful and flexible than MATCH and a great fit for modern Excel tasks involving dynamic lookups. If you’re preparing for interviews or building advanced dashboards, mastering XMATCH can save time and simplify your logic.

    🚀 Want to Master Excel 365 Lookups?

    Enroll in my in-depth Excel training course covering:

    • XMATCH, XLOOKUP, INDEX-MATCH, FILTER, LET, LAMBDA
    • Dashboards, automation, case studies

    👉 Mastering MS Excel – A Comprehensive Training Course

    On sale products