Spreadsheet Formulas

css navigation by Css3Menu.com

Monster vLookUp with Match

So, I have 2 files that are updated weekly from another process I built. They are about 2,200 rows by 66 columns.

I need to make sure I am comparing like weeks and getting the percentage of change.

Monster formula
=IF(ISERROR(VLOOKUP($A14,CurrPlan!$A$2:$BK$2160,MATCH($K$1,CurrPlan!$A$1:$BM$1,0),FALSE)- VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,OrevPlan!$A$1:$BM$1,0),FALSE)/ VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,PrevPlan!$A$1:$BM$1,0),FALSE)),0, (VLOOKUP($A14,CurrPlan!$A$2:$BK$2160,MATCH($K$1,CurrPlan!$A$1:$BM$1,0),FALSE)- VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,PrevPlan!$A$1:$BM$1,0),FALSE))/ VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,PrevPlan!$A$1:$BM$1,0),FALSE))

The Formula:
=IF(ISERROR(VLOOKUP($A14,CurrPlan!$A$2:$BK$2160,MATCH($K$1,CurrPlan!$A$1:$BM$1,0),FALSE) -VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,OrevPlan!$A$1:$BM$1,0),FALSE) /VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,PrevPlan!$A$1:$BM$1,0),FALSE)) ,0,(VLOOKUP($A14,CurrPlan!$A$2:$BK$2160,MATCH($K$1,CurrPlan!$A$1:$BM$1,0),FALSE) -VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,PrevPlan!$A$1:$BM$1,0),FALSE)) /VLOOKUP($A14,PrevPlan!$A$2:$BK$2255,MATCH($K$1,PrevPlan!$A$1:$BM$1,0),FALSE))

And how I build it in VBA
For X=2 to LastRow For V=11 to StCol.MaxCols 'Handle it with Enum -- presently 64 Cells(X, V) = "=IF(ISERROR(VLOOKUP($A" & X & "," & CurSht _ & "!$A$2:$BK$" & lCur & ",MATCH(" & Cells(1, V).Address & "," & CurSht & "!$A$1:$BM$1,0),FALSE)-" _ & "VLOOKUP($A" & X & "," & PrevSht & "!$A$2:$BK$" & lPrev & ",MATCH(" & Cells(1, V).Address & "," & PrevSht & "!$A$1:$BM$1,0),FALSE)" _ & "/VLOOKUP($A" & X & "," & PrevSht & "!$A$2:$BK$" & lPrev _ & ",MATCH(" & Cells(1, V).Address & "," & PrevSht & "!$A$1:$BM$1,0),FALSE)),0,(VLOOKUP($A" & X _ & "," & CurSht & "!$A$2:$BK$" & lCur & ",MATCH(" & Cells(1, V).Address & "," & CurSht _ & "!$A$1:$BM$1,0),FALSE)-VLOOKUP($A" & X & "," & PrevSht & "!$A$2:$BK$" & lPrev _ & ",MATCH(" & Cells(1, V).Address & "," & PrevSht & "!$A$1:$BM$1,0),FALSE))" _ & "/VLOOKUP($A" & X & "," & PrevSht & "!$A$2:$BK$" & lPrev _ & ",MATCH(" & Cells(1, V).Address & "," & PrevSht & "!$A$1:$BM$1,0),FALSE))" Next V Next X


After I had been working on this thing for 6 weeks (lots of other stuff going on); they said, “by the way, will it work if I compare 2 weeks that are a month apart?”

© MMXVII

Updated:  11/07/2017 18:44
This page added:  07 November 2017