韓老師曾經(jīng)寫過(guò)一篇：Excel | VLOOKUP一對(duì)多查找：不連續(xù)相同內(nèi)容對(duì)應(yīng)的多個(gè)數(shù)據(jù)一次提取，今天有朋友說(shuō)：這個(gè)公式寫起來(lái)好麻煩??！
我們的“office天天學(xué)”群里有位高手朋友說(shuō)他更喜歡INDEX+SMALL,那韓老師今天就把INDEX+SMALL這一神組合完成一對(duì)多查找的方法給大家講一講。
結(jié)果是這樣的：
公式實(shí)現(xiàn)
在F2單元格輸入公式：
=INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))&""，以三鍵組合結(jié)束。
公式分步解析
我們以查找“張二”的消費(fèi)記錄為例來(lái)分析：
第一步：
IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B))
用IF函數(shù)，建立一新的數(shù)組，這一新的數(shù)組建立的規(guī)則是：
如果A$2:A$13區(qū)域中的單元格內(nèi)容等于E2單元格內(nèi)容，則返回該單元格所在的行，否則返回整個(gè)工作表的行數(shù)。
所以：此部分返回的數(shù)組是：
{1048576;3;1048576;1048576;1048576;7;1048576;1048576;10;1048576;1048576;13}
可以看到：凡是A列單元格內(nèi)容等于張二的，返回的都是對(duì)應(yīng)的行數(shù)，不等于張二的，返回的都是工作表的行數(shù)1048576。
第二步：
SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)
在第一步形成的數(shù)組中，查找第第一小的數(shù)值。
用ROW(A1)做SMALL函數(shù)的第二個(gè)參數(shù)，即第幾小。
ROW(A1)是一個(gè)動(dòng)態(tài)的數(shù)值，公式往下填充一行，行數(shù)加1，即當(dāng)公式在F2單元格時(shí)，是ROW(A1)，當(dāng)公式填充到F3單元格是，是ROW(A2)，當(dāng)?shù)紽4單元格時(shí)，是ROW(A3)……
這樣，就在第一步的數(shù)組中找到了第1、2、3、4小的值，即3、7、10、13。
第三步：
INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))
當(dāng)公式在F2單元格時(shí)，返回B列第3行的值，即張二的第一次消費(fèi)記錄7478。因?yàn)楣街蠭F部分是數(shù)組計(jì)算，所以公式以三鍵組合結(jié)束。
公式向下填充，得到B列第7、10、13行的值。
第四步：
INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))&""
在最后加上&""，這一步是容錯(cuò)處理。用空單元格與空文本合并返回空文本的特性，將超出結(jié)果數(shù)量的部分不顯示出來(lái)。
