Match Text in two tables

Question

I am trying to match two tables as below.

Table 1:

Article Title	ID Number
University B: Genetic Mutations in Cancer Cells	1
First line Treatments in Lung Cancer by University A	2
Lung Cancer Cases in Hospital of University B	3
First line Treatments in Breast Cancer (Institute C)	4

Table 2:

University Name	Keyword
University of ABC	University B
University of BCD	University A
University of CDE	Institute C

I want to add a column of University Name to Table 1, based on the keywords search in the article title. Given the keywords are highly similar, and can be found in different location in the article title, Vlookup cannot find the match successfully.

Vlookup (even with the option [true], i.e., not for exact match) cannot find the match correctly.

Are there any other formula or free tools can be used?

Many thanks

Answer 1

INDEX/MATCH/SEARCH

Try this array formula (if you don't have Office 365 you need to use Ctrl,Shift+Enter to confirm the formula):

=INDEX(Table2[University Name],MATCH(TRUE,ISNUMBER(SEARCH(Table2[Keyword],[@[Article Title]])),0))

Answer 2

USING FILTER & FIND

Solution Credit to JvdV Sir -- Using O365 & Excel 2021 Function -- FILTER()

• Formula used in cell C3

=FILTER(E$3:E$5,ISNUMBER(FIND(F$3:F$5,A3)),"No Match")

USING LOOKUP & SEARCH

You may try this formula as well, as shown in image below, works in all Excel Versions.

Formula used in cell C3

=LOOKUP(9^9,SEARCH(Table2[Keyword],[@[Article Title]]),Table2[University Name])