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
2 min readJan 15, 2023

--

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!

SUMMARY?

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