Match Text in two tables

user_14077942 0
Viewed 107

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

excel-formula excel
2 Answers

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

enter image description here

user_98140690

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

FORMULA_SOLUTION

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])

FORMULA_SOLUTION

user_81625200
Related