Finding the position of the i-th non blank cell in a vertical range

[vsoler]

Understanding what I need is easy; finding the correct formula perhaps
not so much

Say that my range, in C11:C100 , contains some cells that are not
empty (non blank). Say that in B11:B100 I have the series 1, 2, 3...
and so on until 90.

Imagine that the first cells in my range B11:C100 contain

1 (blank)
2 (blank)
3 AB
4 (blank)
5 ZM
6 (blank)
7 HJ
....

Now, if in cell M11 I input the value 3, that means that I need to
find the 3rd non blank cell in my C column range. That is, my function
should return 7.

I can work with array funsctions, but I would not like to work with
intermediate calculations in additional cells.

Is it impossible what I am looking for?

Thank you for any help that you may supply

 

[T. Valko]

Try this array formula** :

=IF(M11=0,"",IF(COUNTA(C11:C100)<M11,"",INDEX(B11:B100,SMALL(IF(C11:C100<>"",ROW(B11:B100)),M11)-ROW(B11)+1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Duke Carey]

Biff - You can simplify that a little

=IF(M11=0,"",IF(COUNTA(C11:C100)<M11,"",INDEX(B:B,SMALL(IF(C11:C100<>"",ROW(B11:B100)),M11))))

If the OP isn't concerned about checking the boundaries, then

=INDEX(B:B,SMALL(IF(C11:C100<>"",ROW(B11:B100)),M11))

 

[T. Valko]

I'm not sure how INDEX is handled in memory.

Does it index just the used range or does it index the referenced range?

If it indexes the specific referenced range and the range is only a hundred
rows or so, using B:B would seem to be inefficient so I prefer using
specific ranges.

On a related note to using these types of formulas, I've discovered a more
efficient way to calculate the offset.

The "standard method" was like this:

ROW(B11:B100)-ROW(B11)+1

Or, the more robust "user-proof" :

ROW(B11:B100)-MIN(ROW(B11:B100))+1

These are processed as an array.

Moving the offset adjustment to the SMALL function eliminates that much of
the array processing:

SMALL(.......))-ROW(B11)+1
SMALL(.......))-MIN(ROW(B11:B100))+1

 

[Duke Carey]

On big models your concern is justified. In a small one, probably not much
of a difference.

Your second point is VERY interesting.

 

[vsoler]

On big models your concern is justified.  In a small one, probably not much
of a difference.

Your second point is VERY interesting.

Your answers are great!!! I was about to conclude that such a complex
formula was not possible.

Thank you very much.

 

[Ashish Mathur]

Hi,

In cell D11, enter the following formula =
IF(COUNTBLANK(C11)=1,"",COUNTA($C$11:C11)). Copy this formula down to cell
C100. Also, give a heading to this new column - in cell D10, type Revised
No.

In range C102102 type Revised No. and the heading of the column C. In
C103, type 3 and in D103, use the DGET() formula.



--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com