Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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,081
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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,081
Default 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   Report Post  
Old 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,081
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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,081
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding an item in a list & returning a specific value SBW Excel Worksheet Functions 7 April 20th 09 09:54 PM
Getting the position of an item in a pivot table Eden Excel Programming 0 May 6th 08 08:23 PM
Getting a the position of a single item in an array pinkfloydfan Excel Programming 5 July 23rd 07 09:05 PM
finding lowest priced item in a list TimH Excel Discussion (Misc queries) 3 January 13th 06 07:29 PM
UDF to give position of item If list was Alpabetic cae Excel Programming 4 April 15th 04 03:00 PM


All times are GMT +1. The time now is 11:28 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017