Array with multiple results

[Guest]

I would like to have an array formula that returns the order# contained in
column A if column B has an error in it. Is it also possible to enter this
formula into one cell as I don't know how many errors could be in column B.

Example

A B
123456 S-12-04
123457 #N/A
123458 DS-12-06
123459 #N/A
123460 S-12-05

Array formula would return.... 123457, 123459

Thanks in advance

 

[Frank Kabel]

Hi
why not use a filter for this- together with a helper column and the
formula =ISERROR(B1)

 

[Guest]

One way

Assume the data starts in A2

=INDEX($A$1:$A$10,SMALL(IF(ISNA($B$2:$B$10),ROW($B$2:$B$10)),ROW(1:1)))

entered with ctrl + shift & enter

note that INDEX starts with A1 and not A2, that is because row always count
from row 1, so you either use index from the first row (A1) or offset the
small part by 1

copy down until you get a #NUM error

Regards,

Peo Sjoblom

 

[Guest]

This works but returned only the first order#in column A... I understand this
formula except for small refrencing to ROW(1:1) at the end. For this to
display all the errors am I going to have to enter it into multiple cells, I
would really like to avoid that.

Thanks again for all your help.

 

[Guest]

To be more specific, I would like to place this formula in a single cell
only, on another worksheet. I think I might be asking to much.