打破VLOOKUP死症:查找多個結果

VLOOKUP只可查找乎合條件的第1個結果,那如果要查找的結果多過1個,VLOOKUP可做到嗎?

{=INDEX (B:B,SMALL (IF ($B$10:$B$16="A",ROW ($10:$16),2^20),ROW (1:1)))&""}
  • 這是陣列公式(即需輸入公式後同時按Ctrl + Shift + Enter)
IF($B$10:$B$16="A",ROW($10:$16),2^20)
  • 如果B10至B16是A,就取出相對應的數字,否則取出2^20=1048576,即是Excel的最大行數
  • 例如B10是A,便取出10;B11是B,不是A,便取出1048576
  • 所以現時的數字組合是{10;1048576;12;1048576;1048576;15;1048576}
SMALL(IF($B$10:$B$16="A",ROW($10:$16),2^20),ROW(1:1))
  • SMALL的語法是在一個範圍抽第N細的數字。=SMALL(範圍,N)
  • ROW(1:1)會得出1,向下拉動公式便是轉為ROW(2:2),得出2
  • 整句公式即是從{10;1048576;12;1048576;1048576;15;1048576}數字組合找出第1細的數字,即是10。此時向下拉動公式的話,ROW(1:1)會轉為ROW(2:2),便會找出第2細的數字,即是12
INDEX(B:B,SMALL(IF($B$10:$B$16="A",ROW($10:$16),2^20),ROW(1:1)))
  • INDEX是從一個範圍內抽出第N格的內容出來。=INDEX(範圍,N)
  • 由於以上公式SMALL()會得出10,所以便得出「A」。向下拉亦會得出第2個「A」
  • 如果公式在F10,便會得出「John」,F11得出「Tim」
&””
  • 隱藏0值的功用