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]

Someone's just suggested that the issue is that ISBLANK isn't suitable for this because where I have a formula in the 'empty' cells it sees that as the first value? In which case, is there any alternative that I can use?

 

[Aladin Akyurek]

Someone's just suggested that the issue is that ISBLANK isn't suitable for this because where I have a formula in the 'empty' cells it sees that as the first value? In which case, is there any alternative that I can use?

This means "start a fight". The suggested issue does not hold for you had (re-written): IF(ISBLANK(X),"",ROW(X)-ROW(INDEX(X,1,1))+1), which will pick out cells in X that has data excluding empties and blanks (formula cells). By the way, X stands for a multicell range.

 

[apls2350]

Thanks to all of you for the suggestions.

In the end my colleague came up with the below, which isn't perfect because once it picks up the 1st value that's greater than 0 it then takes the value from the adjacent cell in order to capture the second and third but this was taking too long to figure out. Aladin, I'm afraid your most recent suggestion didn't work, possibly because it still uses ISBLANK? and as I mentioned, there's formula in the cells with no value in them.

INDEX(V41:AB41,MATCH(TRUE,V41:AB41>0,0)

second value

INDEX(V41:AB41,MATCH(TRUE,V41:AB41>0,0)+1)

etc.

 

[Aladin Akyurek]

Thanks to all of you for the suggestions.

In the end my colleague came up with the below, which isn't perfect because once it picks up the 1st value that's greater than 0 it then takes the value from the adjacent cell in order to capture the second and third but this was taking too long to figure out. Aladin, I'm afraid your most recent suggestion didn't work, possibly because it still uses ISBLANK? and as I mentioned, there's formula in the cells with no value in them.

INDEX(V41:AB41,MATCH(TRUE,V41:AB41>0,0)

second value

INDEX(V41:AB41,MATCH(TRUE,V41:AB41>0,0)+1)

etc.

I'd advise to post a sample here (not an image) along with the expected results.