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
​

 

[Roderick_E]

This is for only 1 match
In D1 =index(A:A,match("*" & c1 & "*", A:A,0))

This would be better accomplished with a VBA macro/routine

 

[Damo71]

Thanks that works well on the one word search.
VBA is not an option because the end user will often be using a tablet to enter his data.

 

[Damo71]

Bump

Can anyone help me with a formula (not vba) which will return cells that match TWO partial string matches?

For example, My lookup area contains book titles (in A1:100) One of which is "Journey to the Center of the Earth". Lets say I enter the two search words in C1 & C2 ("Journey" & "Earth"). How do I return the result "Journey to the Center of the Earth" in D1?

And is there a way to copy the formula down, say 5 rows so that it will display the first five matches?

Thanks

 

[Peter_SSs]

This sort of thing is tricky, if not impossible, to get exactly right (especially without vba).

- You didn't say whether the 2 words (eg Journey Earth) in C1 need to be in the same order in the title as they are in C1. I have assumed they do (note that cell A18 is not returned by my formula)
- In post 1 you talked about finding words but in post 4 you also talked about string matches. They are different things. I have assumed words and that is why cell A19 is not returned in my results. That cell does contain the string "journey" and the string "earth", but not the word "earth".
- However, the previous point then makes anything with punctuation tricky. Note that cell A6 is also not returned as my formula uses the existence of spaces to determine where the boundary of the word is.

Anyway, see if this is some use. C2 formula is copied down as far as you want.

Excel Workbook
	A	B	C
1	Work		Journey Earth
2	Absalom, Absalom!		A Journey from Earth to Mars
3	A che punto la notte		Journey to the Center of the Earth
4	The Children of Men
5	All Passion Spent
6	Journey's End: Earth
7	Alone on a Wide, Wide Sea
8	A Journey from Earth to Mars
9	Antic Hay
10	Beyond the Mexique Bay
11	Journey to the Center of the Earth
12	Blithe Spirit
13	Butter In a Lordly Dish
14	By Grand Central Station I Sat Down and Wept
15	Carrion Comfort
16	A Catskill Eagle
17	The Children of Men
18	My Earth Journey
19	Journey Beyond The Fire Hearth
20	Cabbages and Kings
Search

 

[markmzz]

Hi!

Maybe the Array Formula (use Ctrl+Shift+Enter to enter the formula) below can help you:

In D1 and copy down until D5 (top 5):

=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})>0,
ROW($A$1:$A$100)),ROWS(D$1:D1))),"")


[TABLE="class: grid, width: 590"]
<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]Word20 Word70 Word53[/TD]
[TD][/TD]
[TD]Word10 Word70 Word65[/TD]
[TD]Word20 Word70 Word53[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Word20 Word71 Word10[/TD]
[TD][/TD]
[TD][/TD]
[TD]Word20 Word71 Word10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Word42 Word84 Word20[/TD]
[TD][/TD]
[TD][/TD]
[TD]Word53 Word24 Word65[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Word53 Word24 Word65[/TD]
[TD][/TD]
[TD][/TD]
[TD]Word65 Word48 Word71[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Word53 Word24 Word66[/TD]
[TD][/TD]
[TD][/TD]
[TD]Word10 Word76 Word75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Word98 Word71 Word46[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Word27 Word21 Word21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Word52 Word68 Word67[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Word65 Word48 Word71[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Word10 Word76 Word75[/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]


Ps: the formula above work with up to three words (with spaces between).

Markmzz

 

[Damo71]

Peter

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

 

[Damo71]

Markmzz

It's probably down to my explanation, but I waslooking only for results that contained all strings, not any.
Thanks so much for your time though. Peter SS has almost nailed it (that "almost" is also down to my not explaining well enough)​

 

[mikerickson]

try
=index(A:A,match("*" & SUBSTITUTE(c1," ","*") & "*", A:A,0))

 

[markmzz]

It's probably down to my explanation, but I waslooking only for results that contained all strings, not any.
Thanks so much for your time though. Peter SS has almost nailed it (that "almost" is also down to my not explaining well enough)​

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