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
​

 

[markmzz]

Try this small modification (Array Formula - use Ctrl+Shift+Enter to enter the formula):

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

Markmzz

Sorry, I forgot one zero:

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

Markmzz

 

[Peter_SSs]

But if I were to say the word order wasn't important, is there an easy fix?

The adjustment to my formula would be:

Excel Workbook
	A	B	C
1	Titles		Journey Earth
2	All Passion Spent		A Journey from Earth to Mars
3	Journey's End: Earth		Earth Beyond the Zen Journey
4	A Journey from Earth to Mars		Journey to the Center of the Earth
5	Antic Hay		My Earth Journey
6	Earth Beyond the Zen Journey
7	Journey to the Center of the Earth
8	Butter In a Lordly Dish
9	By Grand Central Station I Sat Down and Wept
10	Carrion Comfort
11	The Children of Men
12	My Earth Journey
13	Journey To The Fire Hearth
14	Cabbages and Kings
Search (any order)

Other comment:
I think Mark's suggestion from post#11 also does what you want, but just be aware that if the user subsequently inserts any new row(s) at the top of the worksheet, those formulas will then return incorrect results. The following adjustment (still array-entered) should fix that though if you prefer to use that structure
=IFERROR(INDEX($A$1:$A$100,
SMALL(IF(MMULT(--ISNUMBER(SEARCH(" "&TRIM(MID(SUBSTITUTE($C$1," ",REPT(" ",90)),{1,91,181},90))&" "," "&$A$1:$A$100&" ")),{1;1;1})=
(LEN($C$1)-LEN(SUBSTITUTE($C$1," ",""))+1),ROW($A$1:$A$100)-ROW($A$1)+1),ROWS(D$1:D1))),"")

 

[markmzz]

Other comment:
I think Mark's suggestion from post#11 also does what you want, but just be aware that if the user subsequently inserts any new row(s) at the top of the worksheet, those formulas will then return incorrect results. The following adjustment (still array-entered) should fix that though if you prefer to use that structure
=IFERROR(INDEX($A$1:$A$100,
SMALL(IF(MMULT(--ISNUMBER(SEARCH(" "&TRIM(MID(SUBSTITUTE($C$1," ",REPT(" ",90)),{1,91,181},90))&" "," "&$A$1:$A$100&" ")),{1;1;1})=
(LEN($C$1)-LEN(SUBSTITUTE($C$1," ",""))+1),ROW($A$1:$A$100)-ROW($A$1)+1),ROWS(D$1:D1))),"")

Hi Peter!

You are right and thank you for the help with my suggestion.

Markmzz

 

[Damo71]

The adjustment to my formula would be:

Spreadsheet Formulas

Cell Formula C2 =IFERROR(INDEX(A$1:A$100,AGGREGATE(15,6,(ROW(A$1:A$100)-ROW(A$1)+1)/(ISNUMBER(SEARCH(" "&SUBSTITUTE(C$1," "," * ")&" "," "&SUBSTITUTE(A$1:A$100," "," ")&" ")) +ISNUMBER(SEARCH(MID(C$1&" * "&C$1,FIND(" ",C$1),LEN(C$1)+4)," "&SUBSTITUTE(A$1:A$100," "," ")&" "))),ROWS(C$2:C2))),"") <tbody> </tbody>

<tbody>
</tbody>

This is so good (thanks for all answers everyone)

I've adjusted your formula slightly as follows:

Cell	Formula
C2	=IFERROR(INDEX(A$1:A$100,AGGREGATE(15,6,(ROW(A$1:A$100)-ROW(A$1)+1)/(ISNUMBER(SEARCH(SUBSTITUTE(C$1," ","*"),SUBSTITUTE(A$1:A$100," "," "))) +ISNUMBER(SEARCH(MID(C$1&" * "&C$1,FIND(" ",C$1),LEN(C$1)+4)," "&SUBSTITUTE(A$1:A$100," "," ")&" "))),ROWS(C$2:C2))),"")

<tbody>
</tbody>

<strike></strike>
This then works for partial matches and eliminates issues with punctuation.

But there is a bug in there somewhere, and it is this: When I reverse the words then partial matches don't work, and also if I search "Mars Journey" it will return "A Journey from Earth to Mars", but if I search "Mars to" it doesn't return it. "To Mars" is fine, but not backwards. In other words, if the words are next to each other in the title, the backward search doesn't work.

To be honest, it's so picky that it wouldn't bother me if I kept it as is, it will work for 90% of queries, I can just ask the users to mix the words if they don't get the result they're expecting. But, I've tried tinkering with the formulas bit by bit and I can't figure it out. And I guess it would be nice if it were perfect.

Either way, thanks all again; it's brilliant!​

 

[Peter_SSs]

I'm guessing that you have edited the formula incorrectly, but before I go searching for that, do you have any comments on the following?

If you are changing to partial matches, and you are searching for "To Mars", does that mean you happy if the search returns "A Biography of Marshall Patton" which matches the text strings but has nothing to do with the two words being searched for.

 

[Damo71]

Hi Peter, yes, I'm happy with partial matches. That's because almost any search of two words will drastically reduce the options and make the book much easier to find (there are a lot more than a hundred books in reality). The user would have to use his common sense and not search for "and the" or other words that are likely to be in many titles.

But the reason its better to have partial matches allowed is because the user may not be sure whether the book is called "The talented Mr Ripley", "The talent of Mr Ripply" or "Mr Riply is Talented" So a search for "Rip Talent" will return all the options. If it returns "Talent is dead: RIP" as well, then so be it.

Thanks ever so much: I appreciate everyone's help and I have been trying to not bother people by trying to work it through myself. But I'm a bit thick at things like this.
​

 

[markmzz]

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))),"")


[TABLE="class: grid, width: 733"]
<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]Mars to[/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][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A Journey from Earth to Mars[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Antic Hay[/TD]
[TD][/TD]
[TD][/TD]
[TD][/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

 

[Peter_SSs]

Hi Peter, yes, I'm happy with partial matches.

In that case my formula gets reduced a reasonable amount.

Excel Workbook
	A	B	C
1	Titles		Journey Earth
2	All Passion Spent		Journey's End: Earth
3	Journey's End: Earth		A Journey from Earth to Mars
4	A Journey from Earth to Mars		Earth Beyond the Zen Journey
5	Antic Hay		Journey to the Center of the Earth
6	Earth Beyond the Zen Journey		My Earthly Journey
7	Journey to the Center of the Earth		Journey To The Fire Hearth
8	Butter In a Lordly Dish
9	By Grand Central Station I Sat Down and Wept
10	Carrion Comfort
11	The Children of Men
12	My Earthly Journey
13	Journey To The Fire Hearth
14	Cabbages and Kings
Search (any order) (2)

 

[Peter_SSs]

Actually, if it is possible that the lists of titles could include one where the 2 strings are found in both orders (eg "Earth Journey & Earth Experiences") then my previous formula could produces results with none of the required text strings in it.

This modification I think will prevent that happening.

Excel Workbook
	A	B	C
1	Titles		Journey Earth
2	All Passion Spent		Journey's End: Earth
3	Journey's End: Earth		A Journey from Earth to Mars
4	A Journey from Earth to Mars		Earth Journey & Earth Experiences
5	Antic Hay		Journey to the Center of the Earth
6	Earth Journey & Earth Experiences		My Earthly Journey
7	Journey to the Center of the Earth		Journey To The Fire Hearth
8	Butter In a Lordly Dish
9	By Grand Central Station I Sat Down and Wept
10	Carrion Comfort
11	The Children of Men
12	My Earthly Journey
13	Journey To The Fire Hearth
14	Cabbages and Kings
Search (any order) (2)

 

[markmzz]

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