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

Finding a match from an array

W

Wulluby

Member
Hi all,


I feel like I'm chasing my tail with something here and was hoping for some fresh creative insight :)


In A1 I have test1|test2

In A2 I have test2|test3

In B1 I have test2


In C6 I have =IF(IF(ISERROR(FIND("|",A:A )),A:A,LEFT(A:A,(FIND("|",A:A ))-1))=B1,"1","0")

with ctrl+alt+Ent


If B1 has a value of test1 then value of C6=1, if I change B1 to test2 I want for C6 to also show a 1 as test2 can also be found left of the | in A:A but I am not getting that hit.


Any pointers would be appreciated.


Thanks.
 
Hi Wulluby,


Do you want to count number of times the entry in cell B1 appears at the beginning in Column A?


Did you try something like this:

=COUNTIFS(A1:A2,B1&"|*")
 
Hi shrivallabha and thanks for your response.


That will work, and it is a lot simpler than what I was coming up with.


I don't need to find the number of times, only if B1 appears on the left of the | and then a second search on the right of the | but this formula will allow me to find a hit.


Thanks again.
 
You must log in or register to reply here.
Back
Top