IF具有多个Vlookup,每个具有多个输出 [英] IF with multiple Vlookup each with multiple outputs
问题描述
我对下面的excel公式有疑问,
I have a question regarding the below excel formula,
如果满足3个条件,我想在工作流程(2)"表中查找一个值
the case is I want to lookup for a value in 'Workflow (2)' sheet if there're 3 conditions fulfilled
1- C14的值出现在工作流程(2)"工作表的O列中(它可能出现多次)
1- the value of C14 appears in O column in 'Workflow (2)' sheet (it may appear multiple times)
2- E13的值出现在工作流程(2)"工作表的I列中(它可能出现多次)
2- the value of E13 appears in I column in 'Workflow (2)' sheet (it may appear multiple times)
3- D14的值出现在工作流程(2)"工作表的D列中(它可能出现多次)
3- the value of D14 appears in D column in 'Workflow (2)' sheet (it may appear multiple times)
,但是这三个值只有一个匹配项,然后在下面的代码中查看最后一个vlookup
=IFERROR(IF(AND(VLOOKUP(C14,'Workflow (2)'!$O$2:$O$253,1,FALSE)=C14,VLOOKUP(E13,
'Workflow (2)'!$I$2:$J$253,1,FALSE)=E13,VLOOKUP(D14,'Workflow (2)'!$D$26:$D$253,1,FALSE)=D14),
VLOOKUP(D14,'Workflow (2)'!$D$2:$L$253,9,FALSE)),"")
问题是Vlookup总是返回第一个匹配项,这允许公式显示一个值,但这是不正确的,我需要所有vlookups返回所有可能的值,然后将3个条件匹配在一起,以找到我所用的单个公共值会在最后一个vlookup公式中对其进行vlookup
the problem is that Vlookup always return the first match, this allows the formula to display a value but it's not correct, i need all vlookups to return all possible values then match the 3 conditions together to find the single common value that I'll vlookup it in the last vlookup formula
示例:
如果我想使用上述公式,根据同一行中前几列的值查看不同工作表中的红色输出.
If i want to view red output in different sheet based on the values from previous columns in same row, using the above formula.
有什么想法吗?
谢谢.
推荐答案
要使用多个条件搜索值,可以使用以下数组公式:
To search value with multiple criteria you can use following array formula:
=INDEX($D$2:$D$7,MATCH(1,($A$2:$A$7=G2)*($B$2:$B$7=H2)*($C$2:$C$7=I2),0))
按ctrl
+ shift
+ enter
这篇关于IF具有多个Vlookup,每个具有多个输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!