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
CodePudding user response：
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))
CodePudding user response：
USING FILTER & FIND
Solution Credit to 
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])
