當我們使用查詢函式VLOOKUP、LOOKUP、INDEX+MATCH進行查詢時,如果有多個匹配值,如何才能返回最後一個匹配值呢?
本文介紹使用查詢函式查詢最後一個匹配值的五種方法。
這五種方法分為兩大類,第一類先排序再查詢,第二類無需排序直接查詢。
一、案例
如下圖所示,A1:C10為三種產品在不同日期的採購價格表。要求查詢“品2”的最後一次採購價格。
二、解決方法
第一類方法:先對查詢值所在列按升序排序,再使用函式查詢
本例中,查詢值所在列為“產品”列,以“產品”列作為排序依據,按升序排序。排序結果如下圖所示:
對排序後的資料可使用以下三種方法查詢最後一次採購價格。
方法一、VLOOKUP函式
在F2單元格輸入公式:=VLOOKUP(E2,$A$2:$C$10,3,TRUE)
VLOOKUP函式的第3個引數“True”代表近似匹配,VLOOKUP會查詢到小於等於查詢值的最大值。
本例中,對“產品”列按照升序排序,當VLOOKUP查詢到A5單元格的“品2”時,由於“品2”大於“品1”,VLOOKUP函式
會退後
一行查詢到A4單元格的“品1”,並返回C4單元格的值,也就是“品A”的最後一次採購價格。
方法二、LOOKUP函式
在F2單元格輸入公式:=LOOKUP(E2,$A$2:$A$10,$C$2:$C$10)
LOOKUP函式的查詢原理和VLOOKUP函式近似查詢原理類似。
方法三、INDEX+MATCH函式
在F2單元格輸入公式:
=INDEX($C$2:$C$10,MATCH(E2,$A$2:$A$10,1))
本例中,MATCH函式的第三個引數“1”代表近似匹配,MATCH函式會查詢到小於等於查詢值的最大值,查詢值所在列須按升序排序。
第二類方法:無需排序,直接使用函式查詢
如果查詢時,要求不能改變原始資料,那麼我們就不能使用上述先排序再查詢的方
法。以下將介紹兩種無需排序,直接使用函式查詢的方法。
方法一、LOOKUP函式
在F2單元格輸入公式:=LOOKUP(2,1/($A$2:$A$10=E2),$C$2:$C$10)
本例中使用1/($A$2:$A$10=E2)構造查詢區域,返回結果為
{#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!}
LOOKUP函式在“1”和錯誤值“#DIV/0!”構成的陣列中查詢值“2”,LOOKUP函式會查詢到最後一個“1”所在的位置。
方法二、INDEX+MATCH函式
在F2單元格輸入公式:=INDEX($C$2:$C$10,MATCH(2,1/($A$2:$A$10=E2),1))
這是一個數組公式,按Ctrl+Shift+Enter結束公式輸入。
與LOOKUP函式類似,使用1/($A$2:$A$10=E2)構造MATCH函式的查詢區域,
MATCH函式返回最後一個“1”所在的位置。INDEX函式則返回同一行的C列的值。