• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting the list of positions in an array [SOLVED]

K

kumargaurav

New Member
Here is the data i have:

A1=1

A2=2

A3=1

A4=3

A5=2


C1=2


how can i get the list of positions of C1 in the column A(in this case 2 and 5)...
 
Hi Kumar,


Can I assume it as a cross-post or similar question.


http://chandoo.org/forums/topic/getting-lowest-positions-value-in-a-list-depending-upon-an-index


INDEX function used to fetch data from list of positions in an array


So if you need only the Position.. then dont use INDEX function..


I think its enough, or try to resolve by reading below post.

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


Regards,

Deb
 
Hi Kumar ,


The INDEX function is used to retrieve the actual value from a particular position ; the MATCH function returns the position where a match is found ; this is why we usually mention an INDEX + MATCH combination.


However , in your example , since there are duplicates , the MATCH function will not do , since it will only retrieve the position of the first item which matches the value being looked up.


If we go back to the formula Deb posted for your earlier question :


=INDEX(B1:B5,SMALL(IF(A1:A5=C1,ROW(A1:A5)-ROW(A1)+1),1))


the portion which is highlighted , is responsible for giving the position.


In your case , since you want all the possible values , you can use the following formula :


=SMALL(IF($A$1:$A$5=$C$1,ROW($A$1:$A$5)-ROW($A$1)+1),ROW(A1))


Copy this down.


Narayan
 
Thanks Narayan!


Your provided answer and mine.. both are completely same..

the difference is in the attitude..


Sorry... I will try to overcome.. :(


I appreciate..


Regards,

Deb
 
kumargaurav


Cross or multi posting the same question is frowned on. You are getting members to answer on diffrent posts, this is bad manners for you to show to the members who have given their time to answer on a alredy ansewred post.
 
Thanks Debraj,NarayanK for quick reply.Members like you make this forum great...


@bobhc:The question posted is little different from the one i previous one..thats way it is posted separately....
 
You must log in or register to reply here.
Back
Top