Array with multiple results
 

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"James W." schrieb im Newsbeitrag
...
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

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



"James W." wrote:

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

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.

"Peo Sjoblom" wrote:

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



"James W." wrote:

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

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.

"Peo Sjoblom" wrote:

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



"James W." wrote:

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