搜索多个条件时用Excel代替FIND Function [英] Excel substitute for FIND Function when searching for multiple criteria
问题描述
我想查找给定单元格是否具有包含在列表中的值.然后,我想使方程成为动态方程,以便根据列表中的哪个项产生不同的输出.B列中应该有一个方程式(索引/匹配?),该方程式根据A列中的值是否包含C列中的代码之一从D列返回一个值.
I want to find whether a given cell has a value that is included in a list. Then I want to make the equation dynamic so that it results in a different output based on which item in the list it is. There should be an equation in column B (Index/Match?) that returns a value from column D based on whether the value in column A contains one of the codes in Column C.
Column A Column B Column C Column D
ABC12D Equation=$40 ABC $40
XYZ15Q Equation=$20 MNO $30
MNO26P Equation=$30 XYZ $20
推荐答案
设置后,您可以将INDEX/MATCH与FIND一起用作数组公式:
With you setup you would use INDEX/MATCH with your FIND as an array formuls:
=INDEX($D$1:$D$3,MATCH(TRUE,ISNUMBER(FIND($C$1:$C$3,A1)),0))
作为数组公式,退出编辑模式时需要使用Ctrl-Shift-Enter而不是Enter进行确认.
being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.
这篇关于搜索多个条件时用Excel代替FIND Function的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!