Training By Himanshu

Tag: Excel lookup formulas

  • Mastering Excel Lookup Formulas Using ChatGPT: VLOOKUP, INDEX-MATCH, and More

    🧑‍💻 Meet Shantanu – The Problem Solver Who Hated Lookup Errors

    Shantanu was the go-to guy in his company when it came to Excel reports — but there was one thing he dreaded:

    “VLOOKUP is not working.”
    “#N/A is showing again.”
    “How do I fetch values from another sheet?”

    These questions not only came from his team but also popped up in his head during long hours at work.

    One Monday morning, Shantanu had a typical problem:
    Two sheets. One had employee names, the other had bonus amounts.
    He needed to match names and pull bonuses.

    As he began building his old VLOOKUP, he paused.

    “What if I ask ChatGPT?”

    💡 Lesson 1: Ask ChatGPT for a Basic Lookup

    Shantanu typed:

    🗣️ “I have names in column A and want to bring bonus from another sheet where names are in column B and bonus in column C. What’s the VLOOKUP?”

    ChatGPT responded:

    =VLOOKUP(A2, Sheet2!B:C, 2, FALSE)

    And explained:

    “This formula looks for A2 in column B of Sheet2 and returns the value from column C.”

    Shantanu tried it. Boom. It worked!
    No guessing column numbers. No syntax doubts.

    He smiled and whispered:

    “Okay, that was fast.”

    🔁 Lesson 2: Using INDEX + MATCH Instead of VLOOKUP

    Later that day, he needed to look to the left of the lookup column.
    VLOOKUP couldn’t help.

    So he asked:

    🗣️ “How do I look up a value to the left of the lookup column?”

    ChatGPT introduced a new hero:

    =INDEX(C2:C100, MATCH(A2, B2:B100, 0))

    “MATCH finds the row where A2 exists in column B.
    INDEX then fetches the corresponding value from column C.”

    Shantanu paused.
    He had heard of INDEX-MATCH before, but now he understood it for real.

    🧠 Lesson 3: Using LOOKUP for Approximate Matches

    The next day, HR asked Shantanu to categorize employee scores into performance levels.

    90+ = Excellent
    75–89 = Good
    60–74 = Average
    < 60 = Needs Improvement

    He had a list of scores, and he wanted automated labels.

    He asked ChatGPT:

    🗣️ “How do I use a formula to label scores into 4 categories?”

    ChatGPT replied:

    =LOOKUP(A2, {0,60,75,90}, {"Needs Improvement","Average","Good","Excellent"})

    And explained:

    “LOOKUP finds where the score fits in your threshold and returns the matching label.”

    Elegant. Powerful. So readable.
    Shantanu was impressed.

    🧪 Lesson 4: Troubleshooting Lookup Errors with ChatGPT

    But then came the day of doom.
    His formulas worked for 80% of the data, but some rows showed #N/A.

    Instead of panicking, Shantanu typed:

    🗣️ “My VLOOKUP shows #N/A. Can you help fix it?”

    ChatGPT replied:

    “Possible reasons:

    • Lookup value not present in the data
    • Extra spaces
    • Wrong column index
    • Lookup range doesn’t include the value”

    It suggested:

    =IFERROR(VLOOKUP(A2, Sheet2!B:C, 2, FALSE), "Not Found")

    Shantanu cleaned up the data and added TRIM() inside his formula to remove spaces:

    =IFERROR(VLOOKUP(TRIM(A2), Sheet2!B:C, 2, FALSE), "Not Found")

    It worked like a charm.

    📎 Bonus Lesson: Dynamic Lookup with XLOOKUP (for Office 365 Users)

    Shantanu later upgraded to Excel 365.
    He asked ChatGPT:

    🗣️ “Is there something better than VLOOKUP now?”

    ChatGPT excitedly introduced XLOOKUP:

    =XLOOKUP(A2, Sheet2!B:B, Sheet2!C:C, "Not Found")

    “It’s simpler, supports left lookups, error handling, and no need to count columns.”

    Shantanu felt liberated.

    📊 Final Moment – The Presentation

    In his next team meeting, Shantanu showed a dashboard powered entirely by:

    • VLOOKUP (simple fetch)
    • INDEX-MATCH (advanced control)
    • LOOKUP (graded labels)
    • XLOOKUP (clean modern lookups)
    • IFERROR (for user-friendly outputs)

    His manager said:

    “You’ve solved in 3 hours what took others 2 days. How?”
    Shantanu smiled and said:
    “I just asked ChatGPT.”

    📝 Summary: What Shantanu Learned from ChatGPT

    TaskFormulaBenefit
    Basic lookupVLOOKUP()Quick data fetch from right
    Advanced lookupINDEX + MATCHLeft lookups, flexible
    Labeling rangesLOOKUP()Grade/score categorization
    Error handlingIFERROR()Cleaner, readable sheets
    Modern lookupXLOOKUP()One-stop dynamic lookup

    Best selling products

  • 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