Microsoft Excel — ChatGPT — The easiest way to write a complex formula?
Have a friend write it for you! That’s ChatGPT.
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.
