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]

I'm working on Excel 2010..

 

[XOR LX]

Hi.

Try:

=INDEX($V$36:$AB$36,,AGGREGATE(15,6,(COLUMN($V$36:$AB$36)-MIN(COLUMN($V$36:$AB$36))+1)/($V$36:$AB$36<>""),ROWS($1:1)))

Copy down as required.

You will receive #NUM! errors in rows beyond the expected number of returns. These can be resolved to display anything you like if you wish. Let me know.

Regards

 

[Aladin Akyurek]

Always better to word the problem you have instead of putting up a non-working formula.

Control+shift+enter, not just enter:

=INDEX($V$36:$AB$36,SMALL(IF(ISBLANK($V$36:$AB$36),"",COLUMN($V$36:$AB$36)-COLUMN($V$36)+1),1)

 

[JoeMo]

You are working with a range that's a row not a column. Try:

=INDEX(V36:AB36,1,SMALL(IF(ISBLANK(V36:AB36),"",COLUMN(V36:AB36)),1)-COLUMN(V36:AB36)+1)

confirmed with ctrl+shift+enter

 

[apls2350]

Hi, thank you that's worked, that's a huge help. I'm not too worried about the #NUM!, which I haven't come across when applying this to other rows but how do I return a blank value, rather than #REF! when there isn't a 'matching' value in the row?

Regards

 

[apls2350]

Didn't realise there were other suggestions, my reply was for XOR LX but thanks Joe & Aladin.

 

[apls2350]

You are working with a range that's a row not a column. Try:

=INDEX(V36:AB36,1,SMALL(IF(ISBLANK(V36:AB36),"",COLUMN(V36:AB36)),1)-COLUMN(V36:AB36)+1)

confirmed with ctrl+shift+enter

Although to be honest Jo's solution doesn't bring up the error message and is simpler to understand..

But when I copy the formula it doesn't return any values, even though the range is updated to cover the data that I'm trying to capture. What am I missing?

And if I need to find the second number in the range what does the formula need to be?

 

[JoeMo]

My formula will return #NUM! too if all cells are blank. To avoid that use this:

=IFERROR(INDEX(V36:AB36,1,SMALL(IF(ISBLANK(V36:AB36),"",COLUMN(V36:AB36)),1)-COLUMN(V36:AB36)+1),"")

confirmed with ctrl + shift + enter

If you want the second number try:

=IFERROR(INDEX(V36:AB36,1,SMALL(IF(ISBLANK(V36:AB36),"",COLUMN(V36:AB36)),2)-COLUMN(V36:AB36)+1),"")

Either formula can be copied down a column to work on the corresponding cells in a row.

 

[Aladin Akyurek]

Although to be honest Jo's solution doesn't bring up the error message and is simpler to understand..

But when I copy the formula it doesn't return any values, even though the range is updated to cover the data that I'm trying to capture. What am I missing?

And if I need to find the second number in the range what does the formula need to be?

If numbers, better invoke:

A2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($V$36:$AB$36,SMALL(IF(ISNUMBER($V$36:$AB$36),
  COLUMN($V$36:$AB$36)-COLUMN($V$36)+1),ROWS($A$2:A2))),"")