Forum Discussion

fjsh25's avatar
fjsh25
Copper Contributor
Oct 21, 2021

IF/AND formula with an index match.

Hey there I am trying to change formulas to an index match so that when I move the data around it can go to the correct column and row. 

 

So far I have this for the first formula, I have changed it around so many time but it keeps giving me an error. 

=IF(STDEV(INDEX($A$17:$ACX$861,MATCH(A18,$A$17:$A$861,0),MATCH(ADA1&ADA2&ADA3&ADA4&ADA5&ADA6&ADA7&ADA8&ADA9&ADA10&ADA11&ADA12&ADA13,$A$17:$ACX$17,0))=0,1,0),"")

 

Picture below is the original formula in the data. 

 

I am also trying to do the same for this formula below:

=IF(INDEX($A$17:$ACX$861,MATCH(A18,$A$17:$A$861,0),MATCH($ADG$1,$A$17:$ACX$17,0))=0,1,0)*(ADG2,$A$17:$ACX$17))>1,1,0))

 

Original data in cell below:

 

Any suggestions would be great. Thank you!

excel
Formulas and Functions
Macros and VBA

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 23, 2021

    fjsh25 

    If reformat your second formula it looks as

    =IF(
     INDEX( $A$17:$ACX$861,
            MATCH(A18,$A$17:$A$861,0),
            MATCH($ADG$1,$A$17:$ACX$17,0)
     ) = 0 ,
     1,
     0
    ) *
 (ADG2, $A$17:$ACX$17 ) ) > 1, 1, 0) )

    here is more clear it has wrong syntax

Resources