Search Formula?

[marshak]

Ok, I'm not sure if this is possible, but here's what I need.

I have data in cells A17:AC17

In AD17, I need a formula that will look up each cell in the range and give me the contents of that cell if a particular word is found in the cell.

For example:

I need a formula in AD17 that will scan A17:AC17. If the word Email is found in one of those cells in that range, I need the contents of that cell placed into cell AD17.

Hope it can be done. Please let me know.

Thanks so much!!

 

[Aladin Akyurek]

In AD17 enter:

=LOOKUP(9.99999999999999E+307,SEARCH("email",A17:AC17),A17:AC17)

This gives you the last entry that contains the substring "email" if any.

To get multiple entries, control+shift+enter, not just enter:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("email",A17:AC17)),A17:AC17,""))<strike></strike>

 

[Rick Rothstein]

For example:

I need a formula in AD17 that will scan A17:AC17. If the word Email is found in one of those cells in that range, I need the contents of that cell placed into cell AD17.

Can the word Email be found in more than one cell and, if so, what should the formula do?

Will the word Email be the only word in the cell or could it be in there with other text?

 

[Rick Rothstein]

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH("email",A17:AC17)),A17:AC17,""))<strike></strike>

Note: TEXTJOIN is version specific.

 

[marshak]

Unfortunately, the word Email is not be the only word in the cell.

 

[marshak]

Thanks, All!

Your formula worked for me:

=LOOKUP(9.99999999999999E+307,SEARCH("email",A17:AC17),A17:AC17)

THANK YOU!!!

 

[Aladin Akyurek]

Thanks, All!

Your formula worked for me:

=LOOKUP(9.99999999999999E+307,SEARCH("email",A17:AC17),A17:AC17)

THANK YOU!!!

You are welcome. For possible multiple returns, if so desired, use the formula invoking TEXTJOIN, which is available since Excel 2016.

 

[marshak]

Awesome! Will do.