Remember Me?

#1
September 19th 17, 05:58 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2017 Posts: 4
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?

#2
September 19th 17, 06:00 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2015 Posts: 1,182
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
#3
September 19th 17, 06:03 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2017 Posts: 4
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.
#4
September 19th 17, 06:04 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2015 Posts: 1,182
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
#5
September 19th 17, 06:05 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2015 Posts: 1,182
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

#6
September 19th 17, 09:57 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2017 Posts: 4
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. :-)
#7
September 20th 17, 04:32 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2015 Posts: 1,182
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. :-)

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
#8
February 26th 21, 12:22 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Feb 2021 Posts: 1
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. :-)

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post SBW Excel Worksheet Functions 7 April 20th 09 09:54 PM Eden Excel Programming 0 May 6th 08 08:23 PM pinkfloydfan Excel Programming 5 July 23rd 07 09:05 PM TimH Excel Discussion (Misc queries) 3 January 13th 06 07:29 PM cae Excel Programming 4 April 15th 04 03:00 PM

All times are GMT +1. The time now is 07:32 AM.