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

--
Biff
Microsoft Excel MVP


"Duke Carey" wrote in message
...
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)),M1 1))



"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"vsoler" wrote in message
...
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