Why isn't this formula working?
 

I have the following formula (which works find) in cell H2

=VLOOKUP("RED",Scores!K:N,3,0)

I have the following formula in cell H3 which is not working. Any ideas why?

=IF(ISTEXT(INDEX(Scores!$M$1:$M$49997,SMALL(IF(Sco res!$K$1:$K$49997="RED",ROW(INDIRECT("1:50000"))), ROW()-1))),(INDEX(Scores!$M$1:$M$49997,SMALL(IF(Scores!$ K$1:$K$49997="RED",ROW(INDIRECT("1:50000"))),ROW()-1))),"")

Thanks

You'd expect it to wouldn't you, they are so similar!

--
HTH

Bob Phillips

"Jambruins" wrote in message
...
I have the following formula (which works find) in cell H2

=VLOOKUP("RED",Scores!K:N,3,0)

I have the following formula in cell H3 which is not working. Any ideas
why?


=IF(ISTEXT(INDEX(Scores!$M$1:$M$49997,SMALL(IF(Sco res!$K$1:$K$49997="RED",RO
W(INDIRECT("1:50000"))),ROW()-1))),(INDEX(Scores!$M$1:$M$49997,SMALL(IF(Scor
es!$K$1:$K$49997="RED",ROW(INDIRECT("1:50000"))),R OW()-1))),"")

Thanks

Have you tried looking at each section individually for an error.
I have not looked through the logic of the formula but you do have different
sized arrays. I have not verified that all of them which need to be the same
are.
I also assume you entered it with the control-shift-enter.
what is your error message?


"Jambruins" wrote:

I have the following formula (which works find) in cell H2

=VLOOKUP("RED",Scores!K:N,3,0)

I have the following formula in cell H3 which is not working. Any ideas why?

=IF(ISTEXT(INDEX(Scores!$M$1:$M$49997,SMALL(IF(Sco res!$K$1:$K$49997="RED",ROW(INDIRECT("1:50000"))), ROW()-1))),(INDEX(Scores!$M$1:$M$49997,SMALL(IF(Scores!$ K$1:$K$49997="RED",ROW(INDIRECT("1:50000"))),ROW()-1))),"")

Thanks