Hi I have the following sample data
a
b
c
d
a
a
d
What I need is to return the position of the "a" character in the list but not stopping at the first match. Can this be done? I am using MATCH function but this is limited to the first match only?
Thanks for any suggestions
If you want to use 1 formula and you don't have thousands of rows, then
assuming your data is in A2:A8 try:
confirmed with CTRL+SHIFT+ENTER and copied down.
Please Login or Register to view this content.
else, add a helper column in B2:
=IF(A2="a",COUNT(B$1:B1)+1,"")
copied down and then to get data:
=IF(ROWS($A$2:$A2)>MAX($B$2:$B$8),"",MATCH(ROWS($A$1:$A1),$B$2:$B$8,0))
copied down
You can add an INDEX() function around the SMALL or MATCH parts to extract corresponding data from another column
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks