Modifying an existing IF MATCH Formula.

I am currently using this formula to pull data from columns "I" thru "K" in worksheet "D1".

=IF($B8="","",IF(E8="vs",IF(INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),1)="","",INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),1)),IF(E8="@",IF(INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),1)="","",-INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),1)),0)))

I now need to expand this formula to include Columns "G" and "H" in addition to Columns "I" thru "K" in worksheet "D1". I know that: 'D1'!I$3:K$916, has to be changed to 'D1'!G$3:K$916, throughout the formula, but not sure what the other changes need to be made.


As always the forums help is very much appreciated.

Currently formula retrieves data from column I; with changes to range then change to retrieve data from Column I

Please Login or Register  to view this content.

I forgot to add the formulas for the other two columns that I am pulling the data from "D1" into. They are shown below. The columns on this worksheet are the same as those on "D1" , namely "I" thru "K" and now they need to be "G" thru "K".


=IF($B8="","",IF(E8="vs",IF(INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$8587,0),2)="","",INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),2)),IF(E8="@",IF(INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),3)="","",INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),3)),0)))

=IF($B8="","",IF(E8="vs",IF(INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),3)="","",INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),3)),IF(E8="@",IF(INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),2)="","",INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),2)),0)))

So 2 and 3 become 4 and 5 (as per change to formula I posted).

My assumption is that the reason for the number sequencing of 2, 2, 3, 3 and the 3, 3, 2, 2 in the 2nd and 3rd formulas is because of the reference in the formula to the two possible entries in column E.

I just used for Column G (now the 1st column) the new range of 'D1'!G$3:K$916,

=IF($B8="","",IF(E8="vs",IF(INDEX('D1'!G$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),1)="","",INDEX('D1'!G$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),1)),IF(E8="@",IF(INDEX('D1'!G$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),1)="","",-INDEX('D1'!G$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),1)),0)))

For column H (now the 2nd column I used:

=IF($B8="","",IF(E8="vs",IF(INDEX('D1'!G$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),2)="","",INDEX('D1'!G$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),2)),IF(E8="@",IF(INDEX('D1'!G$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),3)="","",-INDEX('D1'!G$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),3)),0)))

For Column"I" now the 3rd column I used:

=IF($B8="","",IF(E8="vs",IF(INDEX('D1'!G$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),3)="","",INDEX('D1'!G$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),3)),IF(E8="@",IF(INDEX('D1'!G$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),2)="","",-INDEX('D1'!G$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),2)),0)))

And then for Columns "J"and "K" I kept the prior range at 'D1'!I$3:K$916 and for "J" I used:

=IF($B8="","",IF(E8="vs",IF(INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$8587,0),2)="","",INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),2)),IF(E8="@",IF(INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),3)="","",INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),3)),0)))

and for "K" I used:

=IF($B8="","",IF(E8="vs",IF(INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),3)="","",INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),3)),IF(E8="@",IF(INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),2)="","",INDEX('D1'!I$3:K$916,MATCH(R8,'D1'!AG$3:AG$916,0),2)),0)))



The above seems to work for all the columns.

Unfortunately, my thought that the above set of formulas had in fact solved my issue to be able to have the data in the five columns present correctly. The problem continues to reside with referencing to the two possible entries in Column E in in worksheet "D1"

When the entry in Column E worksheet "D1" is "vs" then columns in G,H,I,J,K in worksheet D1 are to appear exactly as they are in the worksheet where the formulas reside.

When the entry in Column E worksheet "D1" is "@" then the data in G,H,and I in worksheet D1 are to reverse, if the numbers are positive then they change to the same number but as a negative number, and if they are negative they change to the same number as a positive number.

Also when the entry in column E worksheet D1 is "vs" then the numbers in Columns "J" and "K" present in the same order.... "J' to "J" and "K' to "K".

But if the entry in column E worksheet D1 is "@" then the numbers in columns "J" and "K" in worksheet D1 reverse when they appear on the worksheet with the formulas... the value in column "J" worksheet D1 appears in column "K" and the value in column "K" worksheet D1 appears in column "J"

Again, the current formula works for the 3 columns with the number sequencing in the formulas being 1, 1, 1, 1 then 2, 2, 3, 3 and then 3, 3, 2, 2 but need the
correct sequencing in the formulas when there are 5 columns.