Training By Himanshu

Tag: XLOOKUP Excel

  • XLOOKUP Function in Excel 365: Complete Guide with Examples and Top 20 Interview Questions

    🔍 How to Use the XLOOKUP Function in Excel 365 — Detailed Guide

    ✅ What is XLOOKUP?

    XLOOKUP is a powerful lookup function introduced in Excel 365 and Excel 2021 to replace older functions like VLOOKUP, HLOOKUP, and even INDEX + MATCH. It can search horizontally or vertically, supports approximate/partial matches, and even returns custom messages when no match is found.

    📌 Syntax:

    XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
    ArgumentDescription
    lookup_valueThe value to search for
    lookup_arrayThe range or array to search in
    return_arrayThe range or array to return data from
    if_not_found(Optional) Value to return if no match is found
    match_mode(Optional) 0 = exact match (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard
    search_mode(Optional) 1 = search from first to last (default), -1 = search from last to first

    🧪 Basic Example:

    You have the following data:

    AB
    ProductPrice
    Apple100
    Banana60
    Mango80

    To find the price of Mango:

    =XLOOKUP("Mango", A2:A4, B2:B4)

    ➡️ Result: 80

    🧪 Example with if_not_found:

    =XLOOKUP("Orange", A2:A4, B2:B4, "Not Available")

    ➡️ Result: Not Available (because “Orange” doesn’t exist)

    🧪 Example using wildcard match:

    =XLOOKUP("*man*", A2:A4, B2:B4, , 2)

    ➡️ This matches any product containing “man” (e.g., “Mango”)

    🧪 Reverse Lookup (Bottom to Top):

    =XLOOKUP("Mango", A2:A4, B2:B4, , 0, -1)

    ➡️ Searches from bottom to top. Useful if the latest entry is preferred.

    🧠 20 Interview-Based Questions on XLOOKUP with Answers

    Q1. What is XLOOKUP in Excel?
    A1. XLOOKUP is a modern lookup function that replaces older functions like VLOOKUP and HLOOKUP. It can search vertically or horizontally and offers more flexibility.

    Q2. How is XLOOKUP better than VLOOKUP?
    A2. XLOOKUP allows lookup to the left, supports default return on no match, wildcards, and reverse searches, which VLOOKUP cannot do.

    Q3. Can XLOOKUP search horizontally?
    A3. Yes. You can use it like HLOOKUP by selecting rows instead of columns.

    Q4. What happens if the lookup value is not found?
    A4. If you specify the if_not_found parameter, that value is returned. Otherwise, Excel returns a #N/A error.

    Q5. How can you use XLOOKUP for an exact match?
    A5. Either omit the match_mode (default is exact) or explicitly set it to 0.

    Q6. Can XLOOKUP return an entire row or column?
    A6. Yes, it supports dynamic arrays, so it can return multiple values from a row or column.

    Q7. What does match_mode = 2 mean?
    A7. It enables wildcard matching using * (any number of characters) or ? (single character).

    Q8. What is the purpose of the search_mode parameter?
    A8. It controls the search direction: 1 = top to bottom (default), -1 = bottom to top.

    Q9. Is XLOOKUP case-sensitive?
    A9. No, XLOOKUP is not case-sensitive by default.

    Q10. Can XLOOKUP replace INDEX + MATCH?
    A10. Yes, and it’s simpler to write and understand.

    Q11. What’s the difference between XLOOKUP and LOOKUP?
    A11. LOOKUP is an older function requiring sorted data; XLOOKUP doesn’t and is more robust.

    Q12. What is returned if multiple matches are found?
    A12. XLOOKUP returns the first match, unless search_mode is set to -1 (then it returns the last match).

    Q13. Can XLOOKUP handle blank cells?
    A13. Yes. It will match blank cells if "" is used as the lookup_value.

    Q14. Can XLOOKUP be nested with other functions?
    A14. Yes, it works well inside other functions like IF, SUM, etc.

    Q15. How does XLOOKUP behave in arrays with errors?
    A15. It stops at the first error unless error handling (like IFERROR) is added.

    Q16. Is XLOOKUP available in Excel 2016 or 2019?
    A16. No. XLOOKUP is only available in Excel 365 and Excel 2021.

    Q17. Can XLOOKUP search from right to left?
    A17. Yes, it’s not restricted by column order like VLOOKUP.

    Q18. How to use XLOOKUP for range lookups (approximate match)?
    A18. Set match_mode to -1 (for next smaller) or 1 (for next larger).

    Q19. Can you perform two-way lookups using XLOOKUP?
    A19. Yes. Combine two XLOOKUPs — one for row and one for column.

    Q20. How does XLOOKUP handle dynamic named ranges or structured tables?
    A20. It works seamlessly with dynamic arrays, tables, and named ranges.

    Top rated products

  • Top New Functions in Excel 2021 Explained with Examples: XLOOKUP, FILTER, SORT & More

    🆕 New Functions Introduced in Excel 2021 — Explained with Usefulness

    Microsoft Excel 2021 brought a significant upgrade by introducing several dynamic array functions and smarter lookup and filtering tools. These functions were previously exclusive to Microsoft 365 users but are now part of the perpetual Excel 2021 version, helping users automate tasks, reduce formula complexity, and improve data analysis.

    🔍 1. XLOOKUP

    Purpose: To search for a value in a column or row and return a corresponding value from another column or row.

    Syntax:

    excelCopyEdit=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

    Usefulness:

    • Replaces older functions like VLOOKUP, HLOOKUP, and even INDEX + MATCH.
    • No need to worry about column numbers or data being sorted.
    • Supports exact, approximate, and wildcard matching.
    • Works both vertically and horizontally.

    Example:

    excelCopyEdit=XLOOKUP("Apple", A2:A100, B2:B100, "Not Found")

    This looks for “Apple” in column A and returns the value from column B in the same row.

    🔢 2. XMATCH

    Purpose: Returns the relative position of a value within a range, similar to MATCH but more versatile.

    Syntax:

    excelCopyEdit=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

    Usefulness:

    • Supports reverse search and wildcard matching.
    • Useful for locating the position of a value in arrays, which can then be used with INDEX or CHOOSE.

    Example:

    excelCopyEdit=XMATCH(50, A1:A10)

    Returns the position of 50 in the range A1:A10.

    🧹 3. FILTER

    Purpose: Extracts only the data that meets certain criteria from a range.

    Syntax:

    excelCopyEdit=FILTER(array, include, [if_empty])

    Usefulness:

    • Dynamically displays filtered data in a separate area.
    • Great for dashboards, reporting, or conditional data extraction.
    • Automatically expands or contracts based on the filter results.

    Example:

    excelCopyEdit=FILTER(A2:B10, B2:B10="North")

    Returns only rows where the second column has “North” as the value.

    🔢 4. SORT

    Purpose: Sorts a range or array in ascending or descending order.

    Syntax:

    excelCopyEdit=SORT(array, [sort_index], [sort_order], [by_col])

    Usefulness:

    • Unlike traditional sort, it doesn’t affect the original data.
    • Automatically updates when the source data changes.
    • Useful in dynamic dashboards and data tables.

    Example:

    excelCopyEdit=SORT(A2:B10, 2, -1)

    Sorts the range A2:B10 by the second column in descending order.

    🔢 5. SORTBY

    Purpose: Sorts a range or array based on the values in another array.

    Syntax:

    excelCopyEdit=SORTBY(array, by_array1, [sort_order1], ...)

    Usefulness:

    • More flexible than SORT, as it lets you sort by related fields not in the output.
    • Excellent for sorting one table based on another column or lookup.

    Example:

    excelCopyEdit=SORTBY(A2:B10, C2:C10, 1)

    Sorts A2:B10 based on values in C2:C10 in ascending order.

    🔄 6. UNIQUE

    Purpose: Extracts a list of unique values from a range or array.

    Syntax:

    excelCopyEdit=UNIQUE(array, [by_col], [exactly_once])

    Usefulness:

    • Removes duplicates quickly and dynamically.
    • Especially useful for creating drop-down lists or summary views.

    Example:

    excelCopyEdit=UNIQUE(A2:A100)

    Returns a list of unique values from column A.

    🔢 7. SEQUENCE

    Purpose: Generates a list of sequential numbers in an array format.

    Syntax:

    excelCopyEdit=SEQUENCE(rows, [columns], [start], [step])

    Usefulness:

    • Helpful for creating index numbers, date sequences, or testing data structures.
    • Can generate both 1D and 2D arrays.

    Example:

    excelCopyEdit=SEQUENCE(5,1,10,2)

    Generates 5 numbers starting from 10, increasing by 2 (i.e., 10, 12, 14, 16, 18).

    🎲 8. RANDARRAY

    Purpose: Returns an array of random numbers.

    Syntax:

    excelCopyEdit=RANDARRAY([rows], [columns], [min], [max], [integer])

    Usefulness:

    • Generate sample data for testing or simulations.
    • Can return decimal or whole numbers.
    • Recalculates with every workbook change unless frozen with F9.

    Example:

    excelCopyEdit=RANDARRAY(5, 2, 1, 100, TRUE)

    Generates a 5×2 array of random whole numbers between 1 and 100.

    📦 9. LET

    Purpose: Assigns names to calculation results to reuse within a formula, improving performance and readability.

    Syntax:

    excelCopyEdit=LET(name1, value1, calculation)

    Usefulness:

    • Makes complex formulas more readable.
    • Optimizes performance by computing once and reusing.

    Example:

    excelCopyEdit=LET(x, A1+10, x*2)

    Calculates A1 + 10 once, stores it as x, and then returns x * 2.

    📘 Bonus: Other Useful Functions from Excel 2019 Now Common in Excel 2021

    While not brand-new to Excel 2021, the following were improved and widely adopted:

    • TEXTJOIN – Joins multiple text items with a delimiter.
    • IFS – Replaces complex nested IF formulas.
    • SWITCH – Easier alternative to multiple IF statements for fixed-value cases.

    ⚠️ Not Available in Excel 2021

    Some advanced functions are only available in Microsoft 365 (not Excel 2021), such as:

    • TEXTSPLIT
    • DROP, TAKE, VSTACK, HSTACK
    • WRAPROWS, WRAPCOLS
    • TOCOL, TOROW
    • MAP, REDUCE, SCAN, BYROW, BYCOL

    These are part of the Lambda family and advanced array manipulations introduced later in Excel 365.

    Top rated products