ignoring blank cells in an array forumla

[ENAPo]

I have this formula in a cell:
=IF(ISERROR(INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(5:5)),5)),"",INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(5:5)),5))

If the cell it's pulling the information from is blank it puts a 0 in the cell. I want it to just leave it blank if the cell is blank but am at a loss where to put that in the formula. Hope that doesn't sound confusing.

Thanks!!

 

[ENAPo]

I am not sure what that means. The formula works for what I need. When I try to change it, it doesn't work.

 

[Aladin Akyurek]

I am not sure what that means. The formula works for what I need. When I try to change it, it doesn't work.

You say:

"I have this formula in a cell:
=IF(ISERROR(INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(5:5)),5)),"",INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(5:5)),5))"

In which cell?

 

[Hercules1946]

Your formula calculates the same thing twice and it is not robust...

Aladin
I wonder if you have overlooked the use of IF(ISERROR( which unfortunately requires the primary expression to be specified once for the function to determine if its an error, and then again as the course of action for not an error.
Those persons using Excel 2003 and earlier only have this function

ENAPo
If you have Excel 2007 you can use

=IFERROR(INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(1:1)),5),"") which is a lot shorter.
This formula is adjusted for location on the first line of your extract table.

 

[Aladin Akyurek]

Aladin
I wonder if you have overlooked the use of IF(ISERROR( which unfortunately requires the primary expression to be specified once for the function to determine if its an error, and then again as the course of action for not an error.
Those persons using Excel 2003 and earlier only have this function

I did not. That part proves that something gets computed twice.

ENAPo
If you have Excel 2007 you can use

=IFERROR(INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(1:1)),5),"") which is a lot shorter.
This formula is adjusted for location on the first line of your extract table.

Shorter, but not robust... And it doesn't solve the issue of getting 0's for empty results cells...

 

[ENAPo]

I did not. That part proves that something gets computed twice.



Shorter, but not robust... And it doesn't solve the issue of getting 0's for empty results cells...

Oh...what would be a better formula? I would like to try it out. Thanks!!

 

[markmzz]

Another way:

Array formulas - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF(COUNT(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$R$5000)))>=ROWS($1:5),
INDEX(IF($R$1:$AF$5000="","",$R$1:$AF$5000),SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$R$5000)),ROW(5:5)),5),"")

Or

=IF(COUNT(FIND("|"&$Q$1&"|","|"&$R$1:$AF$5000&"|"))>=ROWS($1:5),
INDEX(IF(INDEX($R$1:$AF$5000,,5)="","",INDEX($R$1:$AF$5000,,5)),SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$R$5000)),ROW(5:5))),"")

Or

=IF(COUNT(FIND("|"&$Q$1&"|","|"&$R$1:$AF$5000&"|"))>=ROWS($1:5),
INDEX(IF(INDEX($R$1:$AF$5000,,5)="","",INDEX($R$1:$AF$5000,,5)),
SMALL(IF(ISNUMBER(FIND("|"&$Q$1&"|","|"&$R$1:$AF$5000&"|")),ROW($R$1:$R$5000)),ROW(5:5))),"")

Markmzz

 

[Aladin Akyurek]

Oh...what would be a better formula? I would like to try it out. Thanks!!

If you would answer the question posed once again in post #12, why not?

 

[Hercules1946]

You say:

"I have this formula in a cell:
=IF(ISERROR(INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(5:5)),5)),"",INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(5:5)),5))"

In which cell?

As I said in post #8 it doesn't matter what cell its entered in. As written (from any cell) it will return the value in column 5 of the index array (Col V)at the intersection of the row containing the 5th instance of the value in Q1.
So... Assuming that its part of a table to extract data from the index array, then (as written) it would be in row 5, column 5 of the extract table (wherever that is). Its usual for the arrays to be processed a column at a time in order to build a "copy" of the information that meets the search criteria. This one is processing all the columns together... why I can't say without seeing the data, but the formula itself is sound as far as I am concerned.

On the issue of the zeros, Ive already proposed 3 ways of dealing with this that are relatively uncomplicated (Also in post #8) and the first of these was accepted (Post #9)

 

[Hercules1946]

I did not. That part proves that something gets computed twice.


And it doesn't solve the issue of getting 0's for empty results cells...

Yes but we can't do anything about that if we are needing to use that function (ie those with 2003 and earlier Excel)
so don't understand what point your making?

See post #18

 

[Hercules1946]

I am not sure what that means. The formula works for what I need. When I try to change it, it doesn't work.

I think your absolutely right ............ as my daddy used to say, "if it ain't broke, don't be trying to fix it !

Apart from the problem of the zeros (which I think we solved) are you having any other problems with it ?