Training By Himanshu

Tag: Excel Storytelling

  • 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

  • How to Rank Values in Excel Ignoring Errors

    It was a regular Monday morning at Shahrokh Enterprises, and Boss Shahrokh was scanning the latest performance report.

    He frowned.

    Shahrokh: “Anushka! Can you come to my cabin for a minute?”

    Anushka, the Excel wiz of the team, walked in with her trusty laptop.

    Shahrokh: “These sales numbers look fine, but look here…”
    “Some cells show #DIV/0! and the ranks are all messed up. I just want to see who’s on top and who’s not — ignore these errors. Can you fix it?”

    Anushka smiled. She had a trick up her sleeve.

    🧠 The Challenge: Rank values but ignore error cells

    Let’s say the data in column A looks like this:

    Sales
    1200
    980
    #DIV/0!
    1150
    #N/A
    950

    If you use the usual formula:

    excelCopyEdit=RANK(A2, A$2:A$7)

    It will throw errors because the range includes non-numeric values.

    💡 The Solution: Use RANK + IF + ISNUMBER + FILTER / IFERROR

    ✅ Option 1: For Excel 365 or 2021 (with FILTER)

    Anushka typed:

    excelCopyEdit=IF(ISNUMBER(A2), RANK(A2, FILTER(A$2:A$7, ISNUMBER(A$2:A$7))), "")

    “This will rank only the numeric values, and skip the error cells, Sir!” she explained.

    • FILTER(A$2:A$7, ISNUMBER(A$2:A$7)) gives only numbers from the range.
    • RANK(..., ...) works only on those.
    • If A2 has an error, it shows a blank instead of another error.

    ✅ Option 2: For older Excel versions (without FILTER)

    excelCopyEdit=IF(ISNUMBER(A2), RANK(A2, IF(ISNUMBER(A$2:A$7), A$2:A$7)), "")

    Press Ctrl + Shift + Enter (array formula for Excel 2016 and below).

    ✨ Boss Shahrokh was impressed.

    Shahrokh: “Anushka, this is brilliant! You’ve saved me from manually checking 300 rows of data.”

    Anushka: “All in a day’s work, Sir. Excel never fails when you ask it the right way.”

    📌 Summary:

    To rank numbers while ignoring errors, wrap your formula in IF(ISNUMBER(...)) and use FILTER or IF + array logic to clean the input range.

    Best selling products

  • How to Use ARRAYTOTEXT Function in Excel 365?

    ✅ What is the ARRAYTOTEXT Function?

    ARRAYTOTEXT(array, [format]) is a text function in Excel 365 that converts an array (range of values or formulas) into a single text string.
    It is helpful when you want to visualize or export the values of a range as a single readable string.

    Syntax

    ARRAYTOTEXT(array, [format])
    • array – Required. The array or range to convert.
    • format – Optional.
      • 0 (default): Returns compact format (best for ranges with just values).
      • 1: Returns strict format (adds quotes and brackets, good for formulas or nested arrays).

    Priya runs a home tiffin service in Pune. Every day, she notes down the dishes prepared in Excel. Her customers often ask for the daily menu via WhatsApp.

    She wants a quick way to convert the dish list in cells (A2:A6) into a single line of text that she can copy and send.

    Data in Excel

    A2: Roti
A3: Paneer Butter Masala
A4: Dal Tadka
A5: Rice
A6: Salad

    Use ARRAYTOTEXT

    =ARRAYTOTEXT(A2:A6)

    Output (Compact format):

    Roti,Paneer Butter Masala,Dal Tadka,Rice,Salad

    ✅ Now Priya can copy-paste this line into WhatsApp quickly.

    🛠 Strict Format Option

    If Priya uses:

    =ARRAYTOTEXT(A2:A6, 1)

    She gets:

    {"Roti";"Paneer Butter Masala";"Dal Tadka";"Rice";"Salad"}

    Useful if she needs to paste it into a formula or some system that requires strict array syntax.

    🎯 When to Use ARRAYTOTEXT?

    • Sending list of items via email/WhatsApp (like Priya).
    • Logging or displaying a formula output for review.
    • Saving snapshot of values in formulas (for audit or export).
    • Creating readable logs from dynamic data.

    ✅ Summary

    FeatureDetails
    Function NameARRAYTOTEXT
    PurposeConverts a range/array to a single text
    Optional ArgumentFormat: 0 (compact), 1 (strict)
    Real-life Use CaseTiffin service menu sharing, student name list export, audit logs

    On sale products