##title##

2020年12月24日

Excel:index+small+if陣列公式,查詢條件後回傳多個值(彌補vlookup或hlookup只能回傳單一值的遺憾)

要回傳值通常會用vlookup或hlookup函數,但只能回傳一個值。要回傳多個值,就必須用上index+small+if再加上陣列公式。

例如:

=INDEX('工作表'!$B:$B,SMALL(IF(('工作表'!$A$2:$A$9998='1221~1225'!C$2)*('工作表'!$D$2:$D$9998='1221~1225'!$B3),ROW('工作表'!$2:$9998),4^8),ROW(indirect("'工作表'!A"&$A3))))
(以上須使用ctrl+shift+enter作為陣列公式,google sheet則是要加ArrayFormula公式) 

「'工作表'!$B:$B」是要回傳的欄。

「('工作表'!$A$2:$A$9998='1221~1225'!C$2)*('工作表'!$D$2:$D$9998='1221~1225'!$B3)」是條件,這邊是透過布林值計算。。

符合條件會回傳對應的行號。不符合條件時,則直接返回4^8,也就是65536,一般的工作表這裏就沒有數據。

在Excel 2003版本,65536就是一列中的最大行號。2010以上是4^10,也就是1048576行, 
至於要回傳值而不是設定空的原因,是因為要供SMALL作排序。

所以數列的結果就會類似:
{1;65536;……;65536;12;13;65536;65536;65536}


參考:

沒有留言:

張貼留言