Formula to match/lookup if part of a string in a c

问题:

I have two text ranges in two columns in excel like that:

____________________________________________________________
  |             A                |     B      |      C     |
------------------------------------------------------------
1 | Mazda Mazda6 i               | Escape     |            |
2 | Ford F150 XL                 | Pathfinder |            |
3 | Ford Freestar SEL            | Jetta      |            |
4 | Ford Explorer Eddie Bauer    | Yaris      |            |
5 | Ford Escape XLT              | Mazda6     |            |
6 | Nissan Pathfinder LE         | F150       |            |
7 | Volkswagen Jetta 2.5         | Tahoe      |            |
8 | Toyota Yaris Base            | Freestar   |            |
9 | Chevrolet Tahoe LS           | Explorer   |            |
------------------------------------------------------------

What I need to do is a formula in C1 to check if any word from range B1:B9 is contained in Cell A1 and if yes, in cell C1 to return the value of the respective matching cell from B1:B9. Then move to C2, check if any word from range B1:B9 is contained in cell A2, and return the matching value from range B1:B9. IF not contained, it can be either #N/A or blank it does not matter.

As example, the formula in C1 should check if any word from B1:B9 matches a string in A1. In this case the returned value in C1 should be Mazda6.

Is there an easy way to do this? I have been trying to find a formula that work on the web for 2 days now and still no luck.

If anyone can help it will be much appreciated.

回答1:

Revised answer due to misunderstanding of question - try this formula in C1 copied down

=IFERROR(LOOKUP(2^15,SEARCH(" "&B$1:B$9&" "," "&A1&" "),B$1:B$9),"")

Assumes only one match at most - B1:B9 should all be populated

This will find matches without getting any "partial matches" like matching red with incredible