Excel LOOKUP具有多个结果 [英] Excel LOOKUP with multiple outcomes
问题描述
具有多个结果的Excel LOOKUP
Excel LOOKUP with multiple outcomes
目前我正在使用以下公式:= LOOKUP(待处理,DK6,A6)
Currently I am using the formula: =LOOKUP("Pending", DK6, A6)
所以如果DK6的单元格内容表示待定,它会显示单元格A6的值。
So if the cell contents of DK6 says "Pending" it displays the value from cell A6.
我需要添加一些更多的子句,一些关于如何构建公式的帮助。我正在寻找的逻辑如下...
I need to add some more clauses and would appreciate some help on how to construct the formula. The logic I am looking for is as follows…
LOOKUP(待定,DK6,A6)>?ELSEIF? > LOOKUP(委派,DK6,留空格)>?ELSEIF? > LOOKUP(拒绝,DK6,将单元格留空)>?ELSEIF? > LOOKUP(空白单元格,DK6,将单元格留空)
LOOKUP("Pending", DK6, A6) > ?ELSEIF? > LOOKUP("Delegated", DK6, leave cell blank) > ?ELSEIF? > LOOKUP("Rejected", DK6, leave cell blank) > ?ELSEIF? > LOOKUP(blank cell, DK6, leave cell blank)
推荐答案
要获取多个匹配行的列表,您可以应用SMALLL()来获取第一个,第二个,第三个等值。
To get lists of multiple matching rows you can apply SMALLL() to pick up the first, second, third, etc. value.
DL2中的公式是
=IFERROR(INDEX($A$2:$A$99, SMALL(INDEX(ROW($1:$98)+($DK$2:$DK$99<>"Pending")*1E+99,,),ROW(1:1))),"")
... or,
=IFERROR(INDEX($A$2:$A$99, SMALL(INDEX(ROW($1:$98)+($DK$2:$DK$99<>DL$1)*1E+99,,),ROW(1:1))),"")
要收集列为空的列A中的单元格有一点不同。
To gather cells in column A where column DK is blank is a little different.
=IFERROR(INDEX($A$2:$A$99, SMALL(INDEX(ROW($1:$98)+(($A$2:$A$99="")+($DK$2:$DK$99<>""))*1E+99,,),ROW(1:1))),"")
这些公式似乎比数组公式更好在另一个线程。列A中的值本质上是唯一的,因此不需要补偿。
These formulas seem to be a better fit than the array formulas provided in another thread. The values in column A are by nature unique so that does not have to be compensated for.
这篇关于Excel LOOKUP具有多个结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!