Excel Index/Match formula but the Index range changes monthly

Question

My formula to return the value of "Total Income" for each company is:

=IFERROR(INDEX(YTD2016!$H$19:$ZZ$19,MATCH(RECAP!$A14,YTD2016!$H$4:$ZZ$4,0)),0)

Works beautifully. No problems at all. LOVE the Index/Match.

The problem is the Index range reference $H$19:$ZZ$19 might change to Row 20 the next month if new lines are added (each month new report pasted in, so it doesn't know cells have moved). If I were doing this every month, it's easy enough to find and replace all and done, but I'm not and the other users can't.

So, I am trying to make sure it works each month, no matter what. I did find the way to return a row number by using the Match("Total Income"...), but is there a way to be able to look up the text string, "Total Income", to get the row number to go in my Index formula?

Since the match string worked bringing back the row number,I even tried putting that equation in the index range (...$H$MATCH("Total Income",'Monthly by Class'!D:D,0). That didn't work as expected, but I was just wanting it to put row 19 there. There must be a way.

^{Index file}

Answer 1

I even tried putting that equation in the index range (...$H$MATCH("Total Income",'Monthly by Class'!D:D,0). That didn't work as expected, but I was just wanting it to put row 19 there. There must be a way.

Indeed there is, and you pretty much had it. You just need to use INDIRECT any time you want to use a formula to generate a cell reference, as opposed to just dropping in the formula in the middle of a cell reference. It's great! INDIRECT uses double quotes to build a cell reference as text.

So if this is your entire, functioning formula:

=IFERROR(INDEX(YTD2016!$H$19:$ZZ$19,MATCH(RECAP!$A14,YTD2016!$H$4:$ZZ$4,0)),0)

Your current hard coded cell reference for the range of INDEX is:

YTD2016!$H$19:$ZZ$19

And we just want to replace the row numbers with a simple MATCH.

Let's look at it this way

YTD2016!$H$ 19 :$ZZ$ 19

and swap in a MATCH formula for the row values

YTD2016!$H$ MATCH("Total Income",'Monthly by Class'!D:D,0) :$ZZ$ MATCH("Total Income",'Monthly by Class'!D:D,0)

Wrap it with INDIRECT and use quotes and ampersands to build the text string

INDIRECT( "YTD2016!$H$"& MATCH("Total Income",'Monthly by Class'!D:D,0) &":$ZZ$"& MATCH("Total Income",'Monthly by Class'!D:D,0) )

Put it all together

INDIRECT("YTD2016!$H$"&MATCH("Total Income",'Monthly by Class'!D:D,0)&":$ZZ$"&MATCH("Total Income",'Monthly by Class'!D:D,0))

and back into its place in the overall function

=IFERROR(INDEX(INDIRECT("YTD2016!$H$"&MATCH("Total Income",'Monthly by Class'!D:D,0)&":$ZZ$"&MATCH("Total Income",'Monthly by Class'!D:D,0)),MATCH(RECAP!$A14,YTD2016!$H$4:$ZZ$4,0)),0)

..and Robert is your mother's brother.

Answer 2

If I understand your problem correctly, you might want have a look at the address function in addition your already working match statement.

as an example,

=ADDRESS(1,1)

would return $A$1

you would put your match as the first argument and the column number as second argument, then put all of that in your index call.

I hope it helps