Finding the position of the nth item in a list

[ilovek...@gmail.com]

I have a feeling I should know this.

Let's say I have a list of 10 numbers in random order.

7
10
3
5
1
9
4
2
8
6

I would like to know the position of the smallest number.

Is there a function where I can give it SMALL(range,1) and have it tell me it's in the 5th position?

[GS]

Have a look at the INDEX() function...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[ilovek...@gmail.com]

Doesn't INDEX do the opposite, Garry? I give it a position, and it returns a value.

[GS]

>> I have a feeling I should know this.
>>
>> Let's say I have a list of 10 numbers in random order.
>>
>> 7
>> 10
>> 3
>> 5
>> 1
>> 9
>> 4
>> 2
>> 8
>> 6
>>
>> I would like to know the position of the smallest number.
>>
>> Is there a function where I can give it SMALL(range,1) and have it tell me
>> it's in the 5th position?
>
> Have a look at the INDEX() function...

Meant to include to look at the MATCH() function as well.

[GS]

Yes, that's why I meant to include the MATCH() function; -it returns a
position!

[ilovek...@gmail.com]

Oh, yes, MATCH! I knew I should know this.

MATCH by itself will do exactly what I want. For some reason my brain froze.

Thanks, Garry. :-)

[GS]

> Oh, yes, MATCH! I knew I should know this.
>
> MATCH by itself will do exactly what I want. For some reason my brain froze.
>
> Thanks, Garry. :-)

Glad to help!

I use MATCH() to return the position of a marker, then use INDEX() to return a
value. This works similar to a lookup function when the lookup value is
unknown.

[JOSEP OLIVA]

I have this formula
=LOOKUP("MDSAP",SORT(I2:I),SORT(C2:C,I2:I,TRUE))
but I need to find all cells containing MDSAP in column I2:2 within a text.
I used "*MDSAP*" instead but it does not work.
Please help !
Thank you.