Finding the position of the nth item in a list
 

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?

Finding the position of the nth item in a list
 

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...

--
Garry

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

Finding the position of the nth item in a list
 

On Monday, September 18, 2017 at 10:00:30 PM UTC-7, GS wrote:
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...

--
Garry

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

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

Finding the position of the nth item in a list
 

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.

--
Garry

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

Finding the position of the nth item in a list
 

On Monday, September 18, 2017 at 10:00:30 PM UTC-7, GS wrote:
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...

--
Garry

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

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

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

--
Garry

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

Finding the position of the nth item in a list
 

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. :-)

Finding the position of the nth item in a list
 

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.

--
Garry

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

Finding the position of the nth item in a list
 

On Wednesday, 20 September 2017 at 05:33:01 UTC+2, GS wrote:
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.
--
Garry

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



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.