Thread: Better than Index & Match formula

Please see attached file, I have a yearly spreadsheet from Jan-Dec. I have two columns that utilize the Index & Match function to extract the variance between two months (the previous month vs the one prior to it) for variance. Is there a more efficient formula to use?

Thank you in advance.

Excellicious::

Hi

Here's another option

=OFFSET(A4,0,MATCH($P$2,$B$2:$M$2,0))-OFFSET(A4,0,MATCH($Q$2,$B$2:$M$2,0))

Another alternative would be to create dynamic named ranges and subtract one from the other. Don't know if that would be any more efficient, as you would use the same approach to create one, then use it as the base for the second.

rylo

I was hoping to eliminate columns O through R and find a formula that only utilizes 1 column. I'm not sure if SUMIF or SUMPRODUCT has that capability to locate the data from the previous month minus the prior month (unfortunately I'm not that savvy). Example, If the current month is June, I would need to find the variance between May vs April.

Thank you for your assistance.

Hi

The formula I gave you only uses your 2 input cells, not the working columns in O:Q. If you put that formula into O4, then drag the cells P2:Q2 up to A1:B1, then copy down the formula you have don't need the additional columns.

If you want to work automatically from the current date then try

=OFFSET(A4,0,MATCH(TEXT(EDATE(TODAY(),-1),"mmm"),$B$2:$M$2,0))-OFFSET(A4,0,MATCH(TEXT(EDATE(TODAY(),-2),"mmm"),$B$2:$M$2,0))

If you don't have the EDATE function, then activate the analysis toolpak standard addin.

rylo

Yeah...Now that is efficiency. I love you guys at Excel tip for making my life easier in soooo many ways. You are a genius..Einstein got nothing on you.

Thanks again.