# 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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### 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
1
Views
137
Replies
2
Views
243
Replies
3
Views
153
Replies
5
Views
43
Replies
1
Views
350

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,713
Messages
5,833,269
Members
430,200
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back