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

Using OR() within Match()

A

AKA

New Member
Hi All,
I am new to this forum and i am sure the question I am about to ask have been asked on his forum many time. I tried searching thru the archives but was getting more lost.
Here's my question. E.g, cell A1 thru A6 contains the following data
Bob
David
John
Mark
Bob
David
I would like to use the Match formula to find the cell position of "Bob" and "David".
In othr works, can we use OR() within Match()?

Many thaks in advance.
 
What would you like to do once you know the position? I only ask because I'm not sure how you want the results to be handled. E.g., do you want a single cell to say:
"1 and 2"
 
Thanks for your reply. I would like to use the cell position so i can use these cell position (a number) for other formula. I tried using =Match(OR("Bob","David"),A1:A6,0) for this but it did not work. Is there any other way to acomplish this?
Thaks again.
 
Hi ,

The Excel worksheet formula which will do what you are looking for , is :

=MATCH(TRUE,ISNUMBER(MATCH($A$1:$A$6,{"Bob";"David"},0)),0)

This will return the position of the first match of either Bob or David.

Narayan
 
Hi Narayan,

I am not sure if this should be an ARRAY formula, it worked for me only after I converted it to an ARRAY formula

kanti
 
Hi AKA,

Try using belo array formula, A1:A6 your data, B1=Bob B2= David
=SMALL(IF(IF(A1:A6=B1,1,0)+IF(A1:A6=B2,1,0),ROW(A1:A6)-ROW($A$1)+1),{1,2})
Enter with Ctrl+Shift+ENter and not just Enter,

This will fetch you array of first two position of Bob & David in the range.

Regards,
 
Hi All,
Sorry guys if my queston was not clear enough. I's my bad. I tried all the formula's above but did not give me what I was looking for. And again, it's my fault for not making my question clear enough for you guys.
Let me take another shot at it:
Column A Column B
Bob
=Match(OR("Bob","David"),A1:a6,0)
David =B1+Match(OR("Bob","David"),A2:A6,0)
John =B2+Match(OR("Bob","David"),A3:A6,0)
Mark =B3+Match(OR("Bob","David"),A4:A6,0)
Bob =B4+Match(OR("Bob","David"),A5:A6,0)
David
=B5+Match(OR("Bob","David"),A6:A6,0)

Result that i was expecting in Column B:
1
5
2
6

Hope I am a bit more clear hear and it make sense to you guys.

Thanks much for all your help,

AKA
 
More details...I have a Status colum with various statuses of empoyees. I need to get the row number of "Absent" or "Sick" Statuses and based on those rows number I will search the employee names.
Hope this helps.

Regards,

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