Convert Array formula to work in Excel 2003

[cows]

Hello all, I have a nicely working array formula which lists unique entries if an adjacent column shows a value of true- it works great on Excel 2007 + but when the file is opened in Excel 2003, it does not work.

The Array formula in A2 is: {=IFERROR(INDEX(SourceData!A:A,SMALL(IF(SourceData!B:B=TRUE,ROW(SourceData!A:A)),ROW(1:1))),"")}
Now, I know right off the bat that IFERROR won't work in Excel 2003, but removing it still doesn't give me any results, only #NUM in Excel 2003. Any ideas on an equivalent Excel 2003 formula?

I've attached a sample document with dummy data if that helps. Thanks in advance!

Array formula.xls

[Jonmo1]

#1, the iferror as you already know..
You would have to do

=IF(ISERROR(formula),"",formula)


The other problem is you can't have entire column references like A:A in an array formula with XL2003
You must specify row #s like A1:A100

[XOR LX]

Hi,

Referencing entire columns (A:A, B:B, etc.) in array formulas is in general not to be recommended.

In 2003, in fact, it's disallowed.

Amend to suitably large references, e.g. A1:A1000, etc.

Regards

[cows]

#1, the iferror as you already know..
You would have to do

=IF(ISERROR(formula),"",formula)


The other problem is you can't have entire column references like A:A in an array formula with XL2003
You must specify row #s like A1:A100

Okay, that is helpful, but I still have something messed up as it only returns "FALSE" in each cell where it should have a match, and blanks where there is not match. My current formula in A2 is: {=IF(ISERROR(INDEX(SourceData!$A$2:$A$25,SMALL(IF(SourceData!$B$2:$B$25=TRUE,ROW(SourceData!$A$2:$A$25)),ROW(1:1)))),"")}

[cows]

Hi,

Referencing entire columns (A:A, B:B, etc.) in array formulas is in general not to be recommended.

In 2003, in fact, it's disallowed.

Amend to suitably large references, e.g. A1:A1000, etc.

Regards

I did not know that - really helpful. I'm still not getting my desired results with that change, I think I'm missing something still.

[Speshul]

You have no entry for value if false. IF/ISERROR you need an argument for if TRUE (is an error) and if FALSE (is NOT error), unlike IFERROR which is just if TRUE (Result)/Else. That's why it's giving you a FALSE when there is no error, the "" you entered shows up where there is an error.


Basically you need to double the length of the formula with an IF(ISERROR) versus IFERROR, because not only do you need to say if the ISERROR comes up TRUE, to put a blank "", but also if the ISERROR comes up FALSE (not an error) then you put the formula again as the last argument in the IF statement, saying "OK Excel, this will work, go ahead!"

Please Login or Register  to view this content.

NOTE: I don't have Excel 2003, so I have no idea if that works. :D

[Jonmo1]

The problem appears to be that you made your range references begin in Row 2.
While before, they began in Row 1 (using entire column references)

This is a problem because this part
ROW(SourceData!$A$2:$A$25)
Is used to create an array of row #s {2,3,4,5,6,7,etc..)

But when you did A:A, it was {1,2,3,4} - started with 1, now it's starting with 2.

I'd go ahead and make the references begin in Row 1.

[Speshul]

Not sure if that is also causing problems, but the results he is getting suggest he is just missing the final IF(ISERROR argument for FALSE

IFERROR says: give me the result of this formula unless it's an error-in that case,
do this instead

IF(ISERROR says: test if this is an error, tell me yes or no (true/false)

if TRUE do this (you have "", which is why you are getting blank cells (the blanks are errors, possibly fixed byu Jonmo1's Post 7 suggestion)

if FALSE do this (this half is optional, if you don't enter anything it will always return a value of FALSE if the formula worked with no error, not the result of the formula. you have to put the formula on this segment to get the result)

[Jonmo1]

Actually, I think we can assume that the error occurs when the Row # of the formula Exceeds the count of TRUE in B:B.
So instead of IFERROR, we can use COUNTIF(B:B,TRUE)

And I'd suggest putting that in it's own cell.

Try

A2 and filled down:
=IF(ROW(1:1)>$D$1,"",INDEX(SourceData!$A$1:$A$25,SMALL(IF(SourceData!$B$1:$B$25=TRUE,ROW(SourceData!$A$1:$A$25)),ROW(1:1))))

D1: =COUNTIF(SourceData!B:B,TRUE)

[Tony Valko]

Here's another one...

Array entered in A2:

=LOOKUP(1E100,CHOOSE({1,2},0,INDEX(SourceData!A:A,SMALL(IF(SourceData!B$2:B$25=TRUE,ROW(SourceData!B$2:B$25)),ROWS(A$2:A2)))))

Then, use either conditional formatting or a custom number format to hide the 0s.

[cows]

You have no entry for value if false. IF/ISERROR you need an argument for if TRUE (is an error) and if FALSE (is NOT error), unlike IFERROR which is just if TRUE (Result)/Else. That's why it's giving you a FALSE when there is no error, the "" you entered shows up where there is an error.


Basically you need to double the length of the formula with an IF(ISERROR) versus IFERROR, because not only do you need to say if the ISERROR comes up TRUE, to put a blank "", but also if the ISERROR comes up FALSE (not an error) then you put the formula again as the last argument in the IF statement, saying "OK Excel, this will work, go ahead!"

Please Login or Register  to view this content.

NOTE: I don't have Excel 2003, so I have no idea if that works. :D

Oh yeah, that was it - your edit to my formula, plus the other suggestions to start at A1 instead of A2 works great, thank you! I actually do understand how the IF error logic works as I use it almost daily, but since this formula has an extra "ROW()" on the end, my brain apparently doesn't know how to process it, which is why I screwed up the syntax.

My final, working formula is: {=IF(ISERROR(INDEX(SourceData!$A$1:$A$25,SMALL(IF(SourceData!$B$1:$B$25=TRUE,ROW(SourceData!$A$1:$A$25)),ROW(1:1)))),"",INDEX(SourceData!$A$1:$A$25,SMALL(IF(SourceData!$B$1:$B$25=TRUE,ROW(SourceData!$A$1:$A$25)),ROW(1:1))))}

And it works in both excel 2003 and 2007 and newer! Thank you all so much!

[Tony Valko]

Another possibility...

If you won't be doing any math operations on these numbers you can have the results be converted to text strings. These numbers look like part numbers or serial numbers. They don't look like you'll be doing any math on them.

This array formula** entered in A2:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(SourceData!A$2:A$25&"",SMALL(IF(SourceData!B$2:B$25=TRUE,ROW(SourceData!B$2:B$25)),ROWS(A$2:A2))-ROW(SourceData!B$2)+1)))

Copy down until you get blanks.