# Finding the nth Value That Meets a Condition

#### apls2350

##### New Member
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..

Thavethe nth Value That Meets a Condition

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### apls2350

##### New Member
I'm working on Excel 2010..

#### XOR LX

##### Well-known Member
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

##### MrExcel MVP
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

##### MrExcel MVP

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

##### New Member
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

##### New Member

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

#### apls2350

##### New Member
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?

Last edited:

#### JoeMo

##### MrExcel MVP
My formula will return #NUM! too if all cells are blank. To avoid that use this:
Rich (BB code):
``=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:

Rich (BB code):
``=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.

Last edited:

##### MrExcel MVP
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:
Rich (BB code):
``````=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))),"")
``````

Replies
10
Views
177
Replies
5
Views
76
Replies
10
Views
127
Replies
5
Views
277
Replies
5
Views
46