0

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.

enter image description here
Index file

0

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

  • According to this you can solve this with vlookup. Might be a better solution in this case. Can't say I've tried it though. – fisknils Mar 23 '17 at 13:30

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.