Search Text of Cell in a range and return the contents

Hi,

My query is similar to this, but there are 2 differences:

1. The keywords in column A and the text strings in column B contain letters and numbers.
2. As returned results I'd like to get the matched cells full content from column B(not the keywords).

Please see attached sample:

Book1.xlsx

I'm newbie to Excel. Any help is really appreciated. Thank you.

Regards
Robert

Hi Knowing,

Welcome to the forum.

Where you want the results ? would it be fine if I return the content in column C basis the entries in column A ?

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Knowing,

Use the below formula :-

Formula:

Please Login or Register  to view this content.

Enter this using Ctrl Shift Enter key combination.


See attached:- Book1.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

In my post #2, I already mentioned about it as

Where you want the results ? would it be fine if I return the content in column C basis the entries in column A ?

you need the output from column B based on Column A.. but since you have placed data in column B which is directly after column A, hence I could not find place to put formula and placed that in column C BUT the formulas are looking in column A only.. and should be dragged down till the data exists in column A. I have observed that you have dragged the formula till the data exists in column B.. which should not be done.

But if you still want to do like that, use below formula:-

=IF(A2="","",INDEX($B$1:$B$14,MIN(IF(ISNUMBER(SEARCH($A2,$B$2:$B$14)),ROW($B$2:$B$14),""))))

See attached:-Book1.1.xlsx


Regards,
DILIPandey

<click on below 'star' if this helps>

yes.. for multiple column search, you can play with the formula part MIN(IF(ISNUMBER(SEARCH($A2,$B$2:$B$14)),ROW($B$2:$B$14),""))

Regards,
DILIPandey

<click on below 'star' if this helps>

You are welcome Knowing..

cheers

Regards,
DILIPandey

<click on below 'star' if this helps>