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

--

## 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.

## 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.