Problem with INDEX formula... too many arguments

[Nikola05]

Does anybody know why this formula does not work? I don't think I have too many arguments in my IF formula as the perimeter is 3 and I only have 3 filled out...

=INDEX('R'!$K$3:$K$51,SMALL(IF('R'!$J$3:$J$51=Comparisons!AO9,ROW('R'!$J$3:$J$51)-ROW('R'!$J$3)+1,VLOOKUP(AO9,'R'!J2:K50,2,0),2),1)

 

[spazm85]

Does anybody know why this formula does not work? I don't think I have too many arguments in my IF formula as the perimeter is 3 and I only have 3 filled out...

=INDEX('R'!$K$3:$K$51,SMALL(IF('R'!$J$3:$J$51=Comparisons!AO9,ROW('R'!$J$3:$J$51)-ROW('R'!$J$3)+1,VLOOKUP(AO9,'R'!J2:K50,2,0),2),1)

You have too many arguments in you IF function. In your formula provided -

Logical test = 'R'!$J$3:$J$51=Comparisons!AO9
Value if True = ROW('R'!$J$3:$J$51)-ROW('R'!$J$3)+1
Value if False = VLOOKUP(AO9,'R'!J2:K50,2,0)

After the "Value if False" you have ",2)" which is the additional argument which it will not accept.

I am unsure of the functionality you are going for but if the Index function is to have a "Col Num" argument = 1, then your formula should read -

=INDEX('R'!$K$3:$K$51,SMALL(IF('R'!$J$3:$J$51=Comparisons!AO9,ROW('R'!$J$3:$J$51)-ROW('R'!$J$3)+1,VLOOKUP(AO9,'R'!J2:K50,2,0)),2),1)

 

[Nikola05]

Thanks for that. I caught that after I was messing around with the formula. The objective was to get the 2nd occurrence of a value given that a condition occurred if not then get the 1st occurrence of that value. This is the formula I came up with after messing around with it. Probably not the most sound formula but works for me.

=IF(H4=0,INDEX('R'!$K$3:$K$51,SMALL(IF('R'!$C$2:$C$51=Comparisons!G4,ROW('R'!$C$2:$C$51)-ROW('R'!$C$3)+1),2),1),VLOOKUP(Comparisons!G4,'R'!$C$2:$K$51,9,0))

 

[Nikola05]

Also Have it written like this

=IF(M4=0,INDEX('R'!$K$3:$K$51,SMALL(IF('R'!$D$3:$D$51=Comparisons!L4,ROW('R'!$D$3:$D$51)-ROW('R'!$D$3)+1),2),1),INDEX('R'!$K$3:$K$51,MATCH(Comparisons!L4,'R'!$D$3:$D$51,0)))

 

[FDibbins]

=IF(H4=0,INDEX('R'!$K$3:$K$51,SMALL(IF('R'!$C$2:$C$51=Comparisons!G4,ROW('R'!$C$2:$C$51)-ROW('R'!$C$3)+1),2),1),VLOOKUP(Comparisons!G4,'R'!$C$2:$K$51,9,0))

Not sure what the vlookup is doing?

Maybe this...
=IF(H4=0,INDEX('R'!$K:$K,SMALL(IF('R'!$C$2:$C$51=Comparisons!G4,ROW('R'!$C$2:$C$51)),rows($A$1:A1)))