Partial Match In Cell Returns Particular Value

[mdsurf]

Hi there! I have a list of names and companies that I am trying to match against another list and have it return specific values. I'm completely and utterly lost at how to even start thinking about this so wondering if someone can help out.

Tab 1:

Name	Company
John Smith	Company A
Jane Doe	Company B
Ryan Reynolds	Company C
Leonardo Dicaprio	Company D
Will Smith	Company E

The next batch of info I have is a sentence which contains the names of the people. I would like to search these sentences, find the name and return the company value if possible.

Sentence	Equation which searches the sentence against the list of names and returns Company Name
John Smith is a great guy	Company A
Have you heard from Jane Doe recently	Company B
Tomorrow is Will Smith birthday	Company E

Is this even possible to do? I am using Microsoft Excel for Mac version 16.16.27

Thank you!

 

[Toadstool]

Hi MDSurf,

Does this do what you want?

MDSurf2.xlsx
	A	B
1	Name	Company
2	John Smith	Company A
3	Jane Doe	Company B
4	Ryan Reynolds	Company C
5	Leonardo Dicaprio	Company D
6	Will Smith	Company E
7
Tab1

MDSurf2.xlsx
	A	B	C
1	Sentence	Result
2	John Smith is a great guy	Company A	Company A
3	Have you heard from Jane Doe recently	Company B	Company B
4	Tomorrow is Will Smith birthday	Company E	Company E
5	Popeye eats spinach		No match
6			No match
Tab2

Cell Formulas
Range	Formula
C2:C6	C2	=IFERROR(INDEX('Tab1'!$B$2:$B$17,AGGREGATE(15,6,ROW('Tab1'!$A$2:$A$17)-ROW('Tab1'!$A$1)/(MATCH("*"&'Tab1'!$A$2:$A$17&"*",A2,0)*('Tab1'!$A$2:$A$17<>"")),1)),"No match")

 

[mdsurf]

Hey! thank you for the detailed reply and that crazy long formula. Unfortunately it didn't work but I think it's close. I think I messed up my explanation / example a little bit unfortunately.

In my example, for tab 2 column B that column is actually blank and where I plan to put the formula. I just put the Company A, Company B etc on what the expected result should be, but in my excel this column is blank.

Also, not sure if it matters but there are ALOT of rows in my sheet so there will be multiple matches for 'John Smith' appears in multiple rows, but different sentences.

Thank you so much for the help!

 

[Peter_SSs]

I am using Microsoft Excel for Mac version 16.16.27

Please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I think that you may need a bit more rigorous formula otherwise you might get some false matches with data like in F6:F7 of my sample data - see col H.
This is my suggestion.
(Not sure about your version on a Mac but if it is not returning correct results, try confirming it with Ctrl+Shift+Enter, not just Enter.
Also, I have put this on one sheet but it should work just as well over two. Post back if you want help to convert it.

BTW, would it ever be possible that two or more names match in one sentence? If so, what do you need to happen? You will see that the two suggestions so far return just one of the relevant companies.

21 10 21.xlsm
	A	B	C	D	E	F	G	H
1	Name	Company				Sentence	Result	Post #2 equiv formula
2	John Smith	Company A				John Smith is a great guy	Company A	Company A
3	Jane Doe	Company B				Have you heard from Jane Doe recently	Company B	Company B
4	Ryan Reynolds	Company C				Tomorrow is Will Smith birthday	Company E	Company E
5	Leonardo Dicaprio	Company D				Popeye eats spinach	No match	No match
6	Will Smith	Company E				John Smithworth is a great guy	No match	Company A
7						Sara-Jane Doerty	No match	Company B
8						John Smith and Jane Doe were there	Company B	Company A
Company lookup

Cell Formulas
Range	Formula
G2:G8	G2	=IFNA(LOOKUP(9^9,SEARCH(" "&A$2:A$6&" "," "&F2&" "),B$2:B$6),"No match")
H2:H8	H2	=IFERROR(INDEX($B$2:$B$17,AGGREGATE(15,6,ROW($A$2:$A$17)-ROW($A$1)/(MATCH("*"&$A$2:$A$17&"*",F2,0)*($A$2:$A$17<>"")),1)),"No match")

 

[Toadstool]

Also, not sure if it matters but there are ALOT of rows in my sheet so there will be multiple matches for 'John Smith' appears in multiple rows, but different sentences.

Thank you so much for the help!

MDSurf,

Yes, I forgot to remove your reference column.

I see @Peter_SSs has provided a shorter formula.

MDSurf2.xlsx
	A	B
1	Sentence	Result
2	John Smith is a great guy	Company A
3	Have you heard from Jane Doe recently	Company B
4	Tomorrow is Will Smith birthday	Company E
5	Popeye eats spinach	No match
6	Will John Smith appear twice?	Company A
7	Will Smith appear twice?	Company E
8	Don't annoy Will Smith	Company E
9	YesThisMatchesJohn Smithalso.	Company A
10	There is no John Smithers on the list	Company A
11		No match
Tab2

Cell Formulas
Range	Formula
B2:B11	B2	=IFERROR(INDEX('Tab1'!$B$2:$B$9999,AGGREGATE(15,6,ROW('Tab1'!$A$2:$A$9999)-ROW('Tab1'!$A$1)/(MATCH("*"&'Tab1'!$A$2:$A$9999&"*",A2,0)*('Tab1'!$A$2:$A$9999<>"")),1)),"No match")