如果某個數據表中存在一對多匹配的情況,用 vlookup 通常只能查找出第一次匹配的結果,如果需要返回第 2 個匹配結果怎麼實現?這個 2 可以是任意指定的次數。
案例:
下圖中的 A、B 列是某公司員工的業績表,這是一個累積的總表,每次有新的業績出來,就順序往下填寫,先報先填。
如何查找出某人第 n 次申報的業績?
![Excel – 有一對多個匹配結果時,如何返回指定的第n個結果?](http://p2.ttnews.xyz/loading.gif)
解決方案 1:
1. 將 C 列設置為輔助列,在 C2 單元格輸入以下公式 --> 下拉複製公式:
=COUNTIF(A$2:A2,A2)
公式釋義:
- 統計相同的名字在列表中第幾次出現
- 需要注意行號的絕對和相對引用
![Excel – 有一對多個匹配結果時,如何返回指定的第n個結果?](http://p2.ttnews.xyz/loading.gif)
2. 在 G2 單元格中輸入以下公式 --> 按 Ctrl+Shift+Enter 回車:
=VLOOKUP(E2&F2,IF({1,0},A:A&C:C,B:B),2,0)
公式釋義:
- A:A&C:C:用 & 符號將 A、C 兩列的值合併起來
- IF({1,0},A:A&C:C,B:B):將邏輯值 {1,0} 作為一組數組參與運算,從而擴充另一組數組 A:A&C:C,B:B,得到的結果為 {"趙鐵錘1",353;"宋大蓮1","134";...}
- vlookup(E2&F2...):將 E2 和 F2 單元格的數據合併起來,成為 "趙鐵錘3",用 vlookup 函數在上述數組中查找並返回第二列的值
- 數組公式,需三鍵結束
以下就是查找結果。
解決方案 2:
1. 構造輔助列,公式見解決方案 1。
2. 在 G2 單元格中輸入以下公式 --> 按 Ctrl+Shift+Enter 回車:
=INDEX(B2:B25,MATCH(E2&F2,A2:A25&C2:C25,0))
公式釋義:
- E2&F2:合併 E2 和 F2 單元格的值
- A2:A25&C2:C25:合併 A2:A25 區域和 C2:C25 區域成一組數組
- match(...):將合併的單元格與數組中的結果匹配,並返回一個表示位置的數字
- index(B2:B25,...):將 B2:B25 區域中,對應位置的值返回為結果
- 這也是個數組公式,因此也要三鍵結束
很多同學會覺得 Excel 單個案例講解有些碎片化,初學者未必能完全理解和掌握。不少同學都希望有一套完整的圖文教學,從最基礎的概念開始,一步步由簡入繁、從入門到精通,系統化地講解 Excel 的各個知識點。
現在終於有了,以下專欄,從最基礎的操作和概念講起,用生動、有趣的案例帶大家逐一掌握 Excel 的操作技巧、快捷鍵大全、函數公式、數據透視表、圖表、打印技巧等……學完全本,你也能成為 Excel 高手。
閱讀更多 Excel學習世界 的文章