匹配/查找单元格中字符串的一部分是否与给定范围内的值相等并返回此值的公式 [英] Formula to match/lookup if part of a string in a cell is equal to a value in a given range and return this value

问题描述

  ____________________________________________________________ 
 | A | B | C | 
 ---------------------------------------------- -------------- 
 1 |马自达Mazda6 i |逃脱| | 
 2 |福特F150 XL |探路者| | 
 3 |福特Freestar SEL |捷达| | 
 4 |福特探险家Eddie Bauer | Yaris | | 
 5 |福特Escape XLT |马自达6 | | 
 6 |日产Pathfinder LE | F150 | | 
 7 |大众捷达2.5 |塔霍| | 
 8 |丰田Yaris基地| Freestar | | 
 9 |雪佛兰Tahoe LS | Explorer | | 
 ---------------------------------------------- -------------- 
  

我需要做的是公式C1中检查单元格A1中是否包含范围B1：B9中的任何单词，如果是，则单元格C1中将返回B1：B9中各个匹配单元格的值。然后移至C2，检查单元格A2中是否包含范围B1：B9中的任何单词，并从范围B1：B9中返回匹配值。
如果没有包含，它可以是＃N / A或空白无关紧要。

例如，C1中的公式应该检查是否有字从B1：B9匹配A1中的字符串。在这种情况下，C1中的返回值应该是 Mazda6 。

有没有简单的方法来做到这一点？我一直在试图找到一个公式，在网络上工作2天，现在仍然没有运气。

如果任何人都可以帮助它，将不胜感激。

解决方案

（$$&$; B $ 1：B $ 9&，& A1&），B $ 1：B $ 9）。 ）

假定最多只有一个匹配--B1：B9应该全部填充

这样可以找到匹配的结果，而不会像红色与难以置信 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.

解决方案

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

这篇关于匹配/查找单元格中字符串的一部分是否与给定范围内的值相等并返回此值的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！