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

—

by

Himanshu
in ,

🧑‍💻 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