首页 \ 问答 \ Excel - 如何根据A列中的多行在B列中查找字符串并返回C列中的相应行?(Excel - How to find strings in Column B based on multiple rows in Column A and return the corresponding row in Column C?)

Excel - 如何根据A列中的多行在B列中查找字符串并返回C列中的相应行?(Excel - How to find strings in Column B based on multiple rows in Column A and return the corresponding row in Column C?)

我在Excel 2007+中有4列。

Col A包含搜索文本(我想要搜索的文本)。 Col B包含我要搜索的所有文本。 Col C包含另一组不会被搜索的值,但如果匹配则将输出到Col D.

基本上,我在Col D中需要一个公式,其逻辑如下:

FOR EACH row in Col A,
IF (a row in B) contains text from each row in A, D = C.

例:

  A     B         C         D
1 dog   dog bone  dog woof  dog woof
2 cat   dog bait  dog meow  dog meow
3 bird  wolf      asd
4 pig   cat       we        we

如您所见,在整个列B中搜索A1。对于Col B中与A1匹配的每一行,C中的相应行输出到D.由于在B1和B2中找到字符串“dog”, C1和C2分别输出到D1和D2。

B3与Col A不匹配或包含任何文本,因此不输出任何内容。

B4包含来自A2的“cat”,因此C4输出到D4。

搜索结果完成后,我将基于Col D过滤表。基本上我想排除Col,其中Col B不包含Col A中任何行的文本,我根据此过滤器检查Col C的数据。

我试过使用INDEX和MATCH以及SEARCH,但我无法绕过公式及其输出。

请帮忙...


I have 4 columns in Excel 2007+.

Col A contains search text (text that I want to search with). Col B contains all the text that I want to search from. Col C contains another set of value that will not be searched, but will be output to Col D if there is a match.

Basically, I need a formula in Col D whereby the logic goes like this:

FOR EACH row in Col A,
IF (a row in B) contains text from each row in A, D = C.

Example:

  A     B         C         D
1 dog   dog bone  dog woof  dog woof
2 cat   dog bait  dog meow  dog meow
3 bird  wolf      asd
4 pig   cat       we        we

As you can see, a search for A1 is performed in the entire column B. For every row in Col B that matches A1, the corresponding row in C is output to D. Since the string "dog" is found in B1 and B2, C1 and C2 are output to D1 and D2 respectively.

B3 does not match or contain any text from Col A, so nothing it output.

B4 contains "cat" from A2, so C4 is output to D4.

After the search result is done, then I will filter the table based on Col D. Basically I want to exclude rows whereby Col B contains no text from any rows in Col A, and I check Col C's data based on this filter.

I've tried to use INDEX and MATCH and SEARCH, but I just can't wrap my head around the formula and its output.

Please help...


原文:https://stackoverflow.com/questions/26544560
更新时间:2019-12-03 09:45

最满意答案

当您使用工作表公式轻松获得所需结果时,我会建议您反对VBA

将以下公式放入单元格D1并向下拖动

{=IF(SUM(IFERROR(FIND($A$1:$A$4,$B1),0)),$C1,"")}

这是一个数组公式,所以省略花括号并使用Ctrl + Shift + Enter输入公式


I would advise against VBA when you can achieve the required results easily with worksheet formulas

Put the following formula into cell D1 and drag down

{=IF(SUM(IFERROR(FIND($A$1:$A$4,$B1),0)),$C1,"")}

It's an array formula so leave out the curly braces and enter the formula using Ctrl+Shift+Enter

2014-10-24

相关问答

更多

显示由fineuploder上传到Amazon S3的图片?(Display images uploaded by fineuploder to Amazon S3?)

该错误消息表明服务器端密钥没有适当的权限来在相关存储桶上进行ListObjects或某些相关调用。 您需要重新评估与服务器端密钥关联的IAM用户/组,并确保它具有所有必需的已分配权限。 The error message suggests that the server-side key does not have proper permissions to make a ListObjects or some related call on the bucket in question. Yo

如何根据数据框和numpy中的协变量对观测进行分类?(How to classify observations based on their covariates in dataframe and numpy?)

在回答你的上一个问题时,我绝对认为pandas是一个很好的工具; 它可以在numpy中完成,但是在处理数据帧时,pandas可以说更直观,并且对于大多数应用程序而言足够快。 pandas和numpy也很好地一起玩。 例如,在您的情况下,您可以使用numpy.select来构建您的pandas列: import numpy as np import pandas as pd # Lay out your conditions conditions = [((df.X1 < 0.5) & (df.

.NET Core注册具有不同参数数量的原始通用(.NET Core Register Raw Generic with Different Number of Parameters)

您可以使用Reflection创建自己的扫描实现,以使用单行注册多个类型。 ServiceCollectionExtensions 在这里,我们实现了一个简单的Scan扩展方法,允许您选择一个接口(无论是通用的还是非泛型的),并且将为所有提供的程序集注册该接口的所有实现。 public static class ServiceCollectionExtensions { public static IServiceCollection Scan( this IServic

什么Java API数据结构对HTML树有用?(What Java API data structure is good for HTML trees?)

不要重新发明轮子,只需使用像Jsoup这样的HTML解析器,你就可以通过使用方法Element#select(cssQuery)的CSS selector来获取你的标签。 Document doc = Jsoup.parse(file, encoding); Elements elements = doc.select(cssQuery); Don't reinvent the wheel, just use an HTML parser like Jsoup, you will be abl

将HTML控件放在Silverlight应用程序的顶部,并使其与应用程序一起滚动(Placing a HTML control over top of a Silverlight Application and have it scroll with the application)

通过捕获滚动事件,我能够调整我的控件的顶级属性,并允许滚动效果。 这是解决方案的基础。 注意:svMainViewer是MainShell.xaml页面中的滚动视图 C#MainShell.xaml.cs代码 public partial class MainShell : UserControl { #region Private Variables private double svHorizontalOffset; private double svVertical

Java Recursion - 来自几个递归调用的输出(Java Recursion - Output from several recursive calls)

这条线 currentSet = new ArrayList<>(currentSet); 是Java方式来调用复制构造函数,即您正在创建新的ArrayList,它最初具有旧列表中的所有元素。 但是,原始列表和新列表是独立的,因此新列表中的任何更改都不会反映到原始列表中。 这在您的算法中很重要,因为这些行: currentSet.add(a); returnSet = combSum(candidates, target, i + a, retu

Javascript:如何将数据字段添加到HTML页面中的所有链接(Javascript: how can i add data field to all the links in a HTML page)

只需使用JQuery循环遍历每个<a>元素并添加如下数据属性: $("a").each(function () { $(this).attr('data-fancybox', 'gallery'); }); 编辑 如果您只想将它添加到某些<a>元素(如容器内),请查看以下示例: (这会将fancybox数据属性仅.do-add-fancybox到.do-add-fancybox容器内的元素) $(".do-add-fancybox a").each(function() { $(

我应该在哪里压平Backbone中要收集的项目数组?(Where should I flatten array of items for collection in Backbone?)

var ListView = Backbone.View.extend({ ... initialize: function() { this.collection.on('reset', this.render, this); } ... }); var // create collection list = new List(), // create view listView = new ListView({

相关文章

更多

最新问答

更多
  • 根据Woocommerce中的自定义字段计算自定义购物车商品价格(Custom cart item price calculation based on dimentions custom fields in Woocommerce)
  • 分开foreach项目并单独打印(Separate foreach items and print them individually)
  • 写模式管道是否同步?(Is write mode pipe synchronous?)
  • 发现可用的Windows Phone强调色和本地化名称(Discover available Windows Phone accent colors and localized names)
  • 使用多线程和WPF更新集合(Update Collection with multithreading and WPF)
  • 如何在字符串的情况下使用同步?(How should the synchronization be used in case of strings?)
  • 打印唯一的行,比较不超过N个字符(Print unique lines, compare no more than N characters)
  • Javascript / jQuery - 如何调用switch case从另一个函数执行(Javascript/jQuery - how to call a switch case to execute from another function)
  • Hbase超时错误不断发生(Hbase timeout errors keep occuring)
  • 如何在Delphi中更改TabControl中活动TAB的颜色(How to change the color of active TAB in a TabControl, in Delphi)
  • 正则表达式:在sublime文本中替换一些PHP代码(Regex: replace some pieces of php code in sublime text)
  • 在带有Scene2D的LibGDX中,如何在按下按钮时连续向右走?(In LibGDX with Scene2D, how can I continuously walk to the right when a button is pressed?)
  • 累加器如何在Haskell中工作?(How do accumulators work in Haskell?)
  • 使用开关检查市场和前缀与正确的货币符号(using switch to check market and prefix with correct currency symbol)
  • 在哪里分配一次使用类?(Where to allocate one time use class?)
  • 如何从两个DateTime / NaiveDateTime获取持续时间?(How do I get Duration from two DateTime / NaiveDateTime?)
  • 解析TimeSpan大于24小时?(Parse a TimeSpan greater than 24 hours? [duplicate])
  • 如何在球拍中本地更改阅读规则?(How to locally change reading rules in racket?)
  • 数据库应该由DI注入时的模拟存储(通过构造函数)(Mock storage when database should be injected by DI (through constructor))
  • 使用maven集成2个eclipse项目(Integrate 2 eclipse projects using maven)
  • 角度ng-repeat不检测变化(Angular ng-repeat not detecting changes)
  • Xaml组件在Silverlight + XNA应用程序的多个页面中可见(Xaml component visible in multiple pages in Silverlight+XNA applications)
  • 如何将`var`变量等同于另一个查询(How to equate `var` variable to another query)
  • 如何设计hyperledger链代码以适合您的业务?(how to design your chaincode of hyperledger to fit for your business?)
  • PHP readfile错误(PHP readfile error)
  • 在Date之后排序列表然后是时间(Sorting list after Date then time)
  • Android内部版本号(Android build number)
  • 在没有预设退出条件的情况下停止无限循环(stopping an infinite loop with no preset exit condition)
  • Phonegap应用程序全屏通过html页面中的按钮(Phonegap Application fullscreen through a button in html page)
  • PAA是否适合在门户网站中自动执行wcm库部署和设置?(Is PAA a good candidate for automating wcm library deployment and setup in portal?)