如何从一个excel列中查找值并在Col B中显示diff值？

   A                                        B (This is what I want)

Apple iPhone                                       Apple
Verizon Galaxy S6 (SM-G920F)                      Samsung
T-Mobile Galaxy S7 Edge (SM-G935F)                Samsung
Apple iPod Touch                                   Apple

i.e. Display Samsung in column B if column A contains the word "Galaxy" and "Apple" if Column A contains the word "Apple".

如果列A包含“Galaxy”和“Apple”,如果列A包含单词“Apple”,则在列B中显示三星。

I want to use an excel formula and not VBA. Can someone please help ?

我想使用excel公式而不是VBA。有人可以帮忙吗?

4 个解决方案

#1

Create a table of the keyword to search and the expected outcome:

创建要搜索的关键字表和预期结果:

Then use this formula:

然后使用这个公式:

=INDEX(E:E,AGGREGATE(15,6,ROW($D$2:$D$3)/(ISNUMBER(SEARCH($D$2:$D$3,A2))),1))

If you do not want the extra table but want to hardcode the array you would do it thus:

如果您不想要额外的表但想要对数组进行硬编码,那么您可以这样做:

=INDEX({"Apple","Samsung"},AGGREGATE(15,6,ROW($1:$2)/(ISNUMBER(SEARCH({"Apple";"Galaxy"},A2))),1))

#2

Here is the formula that you can use in column B:

以下是您可以在B列中使用的公式:

=IF(IFERROR(FIND("Apple",A1) > 0,FALSE), "Apple", IF(IFERROR(FIND("Galaxy",A1)>0,FALSE),"Samsung",""))

Here is the sample data with result:

以下是带有结果的示例数据:

This formula will leave the value blank if it won't find "Apple" or "Galaxy" in corresponding cell in column A.

如果在A列的相应单元格中找不到“Apple”或“Galaxy”,则此公式将保留空白值。

#3

Please try the following:

请尝试以下方法:

=IFERROR(IF(SEARCH("Apple",A1)>0,"Apple"),"Samsung")

#4

You can use formula like this:

你可以使用这样的公式:

=IF(ISNUMBER(SEARCH("Apple";A1));A1;"")

#1