Finding the nth Value That Meets a Condition

apls2350

New Member
Joined
Aug 14, 2014
Messages
26
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
 

Some videos you may like

Excel Facts

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

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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
Joined
May 26, 2009
Messages
17,211
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 14, 2014
Messages
26
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
Joined
Aug 14, 2014
Messages
26

ADVERTISEMENT

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

apls2350

New Member
Joined
Aug 14, 2014
Messages
26
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
Joined
May 26, 2009
Messages
17,211
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,040
Messages
5,526,412
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top