Finding the nth Value That Meets a Condition

[apls2350]

I've taken a formula from this article Excel: Finding the nth Value That Meets a Condition.

Which I'm trying to use to find the first non-empty cell in a range:

Example from article -


=INDEX(range,SMALL(IF(ISBLANK(range),"",ROW(range)),n)-ROW(range)+1)
</pre>
My version -


{=INDEX(V36:AB36,SMALL(IF(ISBLANK(V36:AB36),"",ROW(V36:AB36)),1)-ROW(V36:AB36)+1)}


But it's not returning the first value, which is located in cell AB36.

I think I'm using the second newest version of Excel (apologies for the vague description), which isn't listed as a compatible version in the article so perhaps the formula needs to be adapted?

I am entering the formula as an array..

Thanks in advance.

Thavethe nth Value That Meets a Condition

 

[apls2350]

Thanks Joe, last question - what's causing the formula to return a blank when I copy it to the row below, even though the range is updated to cover the data that I'm trying to capture?

 

[JoeMo]

Thanks Joe, last question - what's causing the formula to return a blank when I copy it to the row below, even though the range is updated to cover the data that I'm trying to capture?

Does the copied formula have curly braces around it when viewed in the formula bar? Is there at least one non-blank cell in the range the formula references?

 

[apls2350]

Yes & yes.

 

[apls2350]

[/URL][/IMG]

 

[apls2350]

OK so I do have the following formula next to the cells with values =IF(ISBLANK(VLOOKUP(A37,'Pipeline 7-9'!$A$4:$AM$112,26,FALSE)),"",VLOOKUP(A37,'Pipeline 7-9'!$A$4:$AM$112,26,FALSE)) but they are blank - I've checked using ISBLANK, the result is TRUE. But when I delete that formula, yours does work??

 

[apls2350]

The other strange thing is that if I don't delete the formula that I've mentioned above but I change your formula from {=IF(J43="YES",IFERROR(INDEX(V43:AB43,1,SMALL(IF(ISBLANK(V43:AB43),"",COLUMN(V43:AB43)),1)-COLUMN(V43:AB43)+1),""),"")} to either:

=IF(J43="YES",IFERROR(INDEX(V43:AB43,1,SMALL(IF(ISBLANK(V43:AB43),"",COLUMN(V43:AB43)),3)-COLUMN(V43:AB43)+1),""),"")

or

=IF(J43="YES",IFERROR(INDEX(V43:AB43,1,SMALL(IF(ISBLANK(V43:AB43),"",COLUMN(V43:AB43))3)-COLUMN(V43:AB43)+3),""),"")

And there's a value in the 3rd column then that value is picked up. Does that help?

 

[Aladin Akyurek]

Try not to get wedded to the first form you came across. You seem to work with the numeric data, so test the relevant cells with ISNUMBER, not with ISBLANK. Try also to anchor the formula directly to the first where you invoke. When copying across, use COLUMNS(...) and when copying down, use ROWS(...).

 

[apls2350]

Try not to get wedded to the first form you came across. You seem to work with the numeric data, so test the relevant cells with ISNUMBER, not with ISBLANK. Try also to anchor the formula directly to the first where you invoke. When copying across, use COLUMNS(...) and when copying down, use ROWS(...).

Thanks for the advice Aladin!

ISNUMBER is FALSE and I've tried copying the formula then changing COLUMnS to ROWS (if that's what you meant?) but the formula still isn't working.

 

[Aladin Akyurek]

Thanks for the advice Aladin!

ISNUMBER is FALSE and I've tried copying the formula then changing COLUMnS to ROWS (if that's what you meant?) but the formula still isn't working.

Your image displays numbers. Are you saying that they don't succeed ISNUMBER test? If so, are these (1) produced by formulas, (2) just manual entries, or (3) copied/downloaded from some web source?

 

[apls2350]

Where there's a number showing ISNUMBER = TRUE. But you were saying that there needs to be a blank value in the range too and I was just confirming that there is an 'empty' cell (although it contains a formula) too.

I've tried copying the range and pasting values only but even that doesn't work. I literally have to select the empty cells which had contained the formula and clear them using delete, in order for Joe's formula to work - frustrating!