Match Multiple Criteria in one cell.

[snadeemshaikh]

Hi,


I am looking for a formula which can match multiple criteria into one cell & if found give criteria as result. I know it would be hard for me to explain so I have posted below an example.

Sheet 1 - From Range A1 till A5 - below are the sentences...


A1 - Chandoo has a wonderful site to make you awesome in Excel

A2 - One of the most respected author/member we have here is Hui.

A3 - Excel Ninja Luke has already crossed 1300 posts

A4 - Chandoo.org blog was started in 2007.

A5 - A questions has been raised on this forum by Nadeem


Sheet 2 - From Range A1 till A4 - below are words...


A1 - Luke

A2 - Nadeem

A3 - Chandoo

A4 - Hui

Formula Required in Sheet 1 - Range B2 onwards.


I need a formula which will search for all words from Sheet 2 (Luke, Nadeem, Chandoo, Hui) & if match found will deliver the match as result.


So the result would be like below when formula applied in B Column.


Chandoo has a wonderful site to make you awesome in Excel - Forumla Result Chandoo

One of the most respected author/member we have here is Hui. - Forumla Result Hui

Excel Ninja Luke has already crossed 1300 posts - Forumla Result Luke

Chandoo.org blog was started in 2007. - Forumla Result Chandoo

A questions has been raised on this forum by Nadeem - Forumla Result Nadeem


I hope this has make sense, if not do apologize me for my langauge & I will try to post an example sheet. (which is very difficult for me as I am in office.)

 

[Luke M]

Try this array formula in sheet2, B2:

=INDEX(Sheet1!A:A,MIN(IF(ISNUMBER(SEARCH(A2,Sheet1!$A$1:$A$20)),ROW(Sheet1!$A$1:$A$20))))&" - "&A2


I believe you were wanting the criteria returned as part of the formula result. If I misunderstood, formula is just:

=INDEX(Sheet1!A:A,MIN(IF(ISNUMBER(SEARCH(A2,Sheet1!$A$1:$A$22)),ROW(Sheet1!$A$1:$A$22))))


Note that array formulas must be confirmed using Ctrl+Shift+Enter.

 

[Luke M]

Try this array formula:

=INDEX(Sheet2!A:A,MIN(IF((ISTEXT(Sheet2!$A$1:$A$20))*(ISNUMBER(SEARCH(Sheet2!$A$1:$A$20,A1))),ROW(Sheet2!$A$1:$A$20))))


Array formulas must be confirmed using Ctrl+Shift+Enter.


PS. Excellent description of problem and desired results, thank you!

 

[snadeemshaikh]

That was awesome Luke. Thanks a ton for helping me out with this.


You should be named Excel God either than Excel Ninja.

 

[Luke M]

You're welcome, snadeemshaikh.

The one thing with knowing so much about Excel, is that I learn how much more I need to learn about Excel. =P

 

[NARAYANK991]

Hi Nadeem ,


Just by the way , what should happen if a match is not found ?


Narayan

 

[NARAYANK991]

Hi Nadeem ,


Can you try this formula and see what it does ?


=INDEX(Sheet2!$A$1:$A$20,MATCH(1,ISTEXT(Sheet2!$A$1:$A$20)*ISNUMBER(SEARCH(Sheet2!$A$1:$A$20,Sheet1!A1)),0))


Enter this using CTRL SHIFT ENTER. Copy this down to the remaining cells.


Narayan