Microsoft Excel — ChatGPT — The easiest way to write a complex formula?

Have a friend write it for you! That’s ChatGPT.

Don Tomoff
Let’s Excel


The #data structure View

The Process

Prior to commencing this, I created three Named Ranges for the data set in Excel.

  • pt_datasource — the range housing all of the values.
  • pt_rowsource — the far left column reflecting Product names.
  • pt_columnsource — the top row reflecting the month-end dates.

These are then referenced in the request to ChatGPT.

Here’s what I asked it to do:

1️⃣ “Create an Excel formula that extracts a value from range named “pt_datasource” where the value is in row 10 and column 10”

For step 1, I used row and column placeholders just to see what it returned.

Response? — The formula would be:

=INDEX(pt_datasource,10,10) ✔️

Perfect. An INDEX function for this makes perfect sense.

Next, let’s clean it up by automating the row and column lookup depending on cell variables I created.

2️⃣ “Modify this formula to replace the column and row values with an automatic lookup ability. The row match looks at cell B5 and finds the appropriate row using a range named “pt_rowsource”. The column match looks at cell C4 and finds the appropriate column match using a range named “pt_columnsource”.

Response? — The formula would be:

=INDEX(pt_datasource,MATCH(B5,pt_rowsource,0),MATCH(C4,pt_columnsource,0)) ✔️

Perfect. Using the MATCH functions to accomplish the 2-way lookup is the best way to go.

I then pasted it into my worksheet to test it. No problem. Worked like a charm.

This post was originally posted on LinkedIn — follow me on LinkedIn and follow #twinztalk as well!


I was able to write an normally complex INDEX/MATCH function by just explaining what I wanted to do. 💪💯

NO explaining that I wanted an INDEX MATCH formula.

#chatgpt may not be perfect, but it’s good enough to make a serious productivity leap in my Excel spreadsheet models development.

And that’s HUGE for me.

About Don

“It’s time for different”

Connect with me!

LinkedIn, Flipboard, Twitter, Snapchat



Don Tomoff
Let’s Excel

It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics #genai #chatgpt