Return valuse that contain a string or two strings

[Damo71]

Hi

I have a list of book titles in A1:100.

In cell C1 the user enters a string of one word (and, if possible, two words separated by a space*)

In cells D1 to D5 I need formulas to return the full book titles that contain the word or words the user has entered.

*e.g. if "Journey to the Center of the Earth" is in A1, and the user enters "Journey Earth" in C1, then D1 will return

"Journey to the Center of the Earth", the next matching entry will be in D2 and so on, up to 5 matches. If not possible with multiple words could you please help me with just a single string?

How can I do this please?

Many thanks
​

 

[Damo71]

That's amazing. You've all been fantastic.

The lesson I think I learnt there is to give as much detail as possible.

I thought if I explain too much and people see a huge post they'll just go to the next one. So I figured I'll get the answers and then do modifications (like extend the book list, change the references to where they really are etc) In trying to simplify I created more work for you and ages for me.

So I apologise for that but thank you for a perfect and very helpful result.

 

[Peter_SSs]

.. thank you for a perfect and very helpful result.

You are very welcome.


@markmzz
Formula for post#20?

 

[markmzz]

Post #17

Hi Damo71,

I think that my suggestion can help you. Look at this:

Array Formula - use Ctrl+Shift+Enter to enter the formula

=IFERROR(INDEX($A$1:$A$100,
​

SMALL(IF(MMULT(--ISNUMBER(SEARCH(" "&TRIM(MID(SUBSTITUTE($C$1," ",REPT(" ",90)),{1,91},90))&" "," "&$A$1:$A$100&" ")),{1;1})=
(LEN($C$1)-LEN(SUBSTITUTE($C$1," ",""))+1),ROW($A$1:$A$100)-ROW($A$1)+1),ROWS(D$1:D1))),"")

Post #20

Another way.

Here is my suggestion with Peter's example:


[TABLE="class: grid, width: 765"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]All Passion Spent[/TD]
[TD][/TD]
[TD]Journey Earth[/TD]
[TD]A Journey from Earth to Mars[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Journey's End: Earth[/TD]
[TD][/TD]
[TD][/TD]
[TD]Earth Beyond the Zen Journey[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A Journey from Earth to Mars[/TD]
[TD][/TD]
[TD][/TD]
[TD]Journey to the Center of the Earth[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Antic Hay[/TD]
[TD][/TD]
[TD][/TD]
[TD]My Earth Journey[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Earth Beyond the Zen Journey[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Journey to the Center of the Earth[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Butter In a Lordly Dish[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]By Grand Central Station I Sat Down and Wept[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Carrion Comfort[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]The Children of Men[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]My Earth Journey[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Journey To The Fire Hearth[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Cabbages and Kings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]*******************************************[/TD]
[TD]**[/TD]
[TD]***********************[/TD]
[TD]*******************************[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

@markmzz
Formula for post#20?

Hi Peter,

Sorry, about that.

​

With the formula of the post #17.

Markmzz

 

[Peter_SSs]

With the formula of the post #17.

Ah, sorry, given that the OP had moved to wanting to search for partial matches (posts 14 & 16) which I addressed in posts 18 & 19 and then you said "Another way", I thought you were saying "another way" to do that.