2.2.6 高效的匹配查找函數

快速查找匹配某個單元格或者區域的數值，可以用Excel函數中的匹配查找相關函數，例如，CHOOSE、VLOOKUP、HLOOKUP、LOOKUP、MATCH、INDEX、OFFSET、INDIRECT等。工作中經常會碰到匹配數據的場景，例如，從大量用戶的消費數據中匹配出某幾個人的消費數據，此時可以用VLOOKUP、LOOKUP或MATCH結合INDEX函數等方法進行數據匹配。此外，基于某個起點進行位移而獲取其他單元格區域的數據可以使用OFFSET函數。下面通過實例對常用的匹配查找函數進行舉例說明。

1.CHOOSE函數

功能說明：根據索引號index_num返回數值參數列表中的數值。

語法：CHOOSE(index_num,value1,[value2], …)

參數：

● index_num必需。用于指定所選定的數值參數。index_num必須是介于1到254的數字，或是包含1到254的數字的公式或單元格引用。

● value1,[value2],…,value1必需，后續值可選。1到254個數值參數，CHOOSE將根據index_num從中選擇一個數值或一項要執行的操作。參數可以是數字、單元格引用、定義的名稱、公式、函數或文本。

示例：以某班級的學生成績表為例，數據如表2-9所示。字段包括姓名、成績。數據位于區域A1:B5，區域D2:D4是CHOOSE函數相關公式，區域E2:E4是計算說明，區域F2:F4是計算結果。

公式與步驟：

● 示例一：單元格F2內輸入公式“=CHOOSE(2,A2,A3, A4,A5)”。

● 示例二：單元格F3內輸入公式“=CHOOSE(3,B2,B3,B4,B5)”。

● 示例三：單元格F4內輸入公式“=SUM(CHOOSE(2,A2:A5,B2:B5))”，結果如圖2-85所示。

提示：

● index_num必須是介于1到254的數字。

● 如果index_num是一個數組，則在計算函數CHOOSE時，使用數組公式，可以返回對應的每一個值。

● CHOOSE函數的value參數除了單個數值或單個單元格引用之外，也可以為區域引用。如圖2-85中單元格F4里面的公式就是對區域的引用。

2.VLOOKUP函數

功能說明：將查找值在某個區域中的第一列進行查找，根據列號返回右側第col_index_num列與查找值處于同行的數值。

語法：VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

參數：

● lookup_value必需。要查找的值。

● table_array必需。要在其中查找值的區域。

● col_index_num必需。區域中包含返回值的列號。

● range_lookup可選。精確匹配或近似匹配，精確匹配用0或FALSE指代，近似匹配用1或TRUE指代。參數省略時默認為近似匹配。

示例：以某學校的學生信息成績表為例，數據如表2-10所示。字段包括ID、班級、姓名、性別、語文成績、數學成績、英語成績。要求根據姓名來查找學生的語文、數學、英語成績。

公式與步驟：單元格E11內輸入公式“=VLOOKUP($D11,$C$1:$G$8,COLUMN(C1), 0)”，然后向右向下拖拽復制公式。這里lookup_value所在的單元格D11要絕對引用列（$D11）；table_array選擇的范圍C1:G8也要絕對引用行和列（$C$1:$G$8）；由于查找的學生的語文、數學、英語成績的位置和數據源一致，因此col_index_num可以選擇用COLUMN函數進行相對引用；range_lookup參數設置成0，進行精確匹配，結果如圖2-86所示。

提示：

● lookup_value可以為模糊值，例如，查找姓李的學生的成績，姓名使用“李*”。

● table_array的第一列必須是lookup_value查找范圍的所在列，范圍選擇方向從左往右。

● VLOOKUP函數的column_index_num必須是大于0的整數。

● 如需精確匹配，最后一個參數設置成0或FALSE。

● 當查找的數據在查找范圍內有重復的時候，返回查找范圍內第一列首次出現的查找值所對應的數值。

3.HLOOKUP函數

功能說明：將查找值在某個區域中的第一行進行查找，根據行號返回下方第row_index_num行與查找值處于同列的數值。

語法：HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

參數：

● lookup_value必需。要查找的值。

● table_array必需。要在其中查找值的區域。

● row_index_num必需。區域中包含返回值的行號。

● range_lookup可選。精確匹配或近似匹配，精確匹配用0或FALSE指代，近似匹配用1或TRUE指代。參數省略時默認為近似匹配。

示例：以某企業的員工薪資表為例，數據如表2-11所示。字段名稱位于第一列，包括工號、姓名、薪資、獎金。要求根據姓名來查找員工的薪資和獎金。

公式與步驟：單元格G7內輸入公式“=HLOOKUP($F7,$A$2:$H$4,COLUMN(B1),0)”，然后向右向下拖拽復制公式，結果如圖2-87所示。

提示：

● HLOOKUP與VLOOKUP功能非常相似，都是進行匹配查找的函數，且函數參數相同。唯一的區別是VLOOKUP函數是在列上面進行查找，而HLOOKUP函數是在行上面查找。

● lookup_value可以為模糊值，例如，查找姓劉且名字長度為2的員工對應的薪資和獎金，姓名可以使用“劉?”。

● table_array的第一行必須是lookup_value查找范圍的所在行，范圍選擇方向從上往下。

● HLOOKUP函數的row_index_num必須是大于0的整數。

● 如需精確匹配，最后一個參數設置成0或FALSE。

● 當查找的數據在查找范圍內有重復的時候，返回查找范圍內第一行首次出現的查找值所對應的數值。

4.LOOKUP函數

功能說明：將查找值在一行或一列進行查找，返回一行或列中的相同位置的數值。LOOKUP函數可以進行精確匹配和近似匹配。

語法：

● LOOKUP(lookup_value,array)

● LOOKUP(lookup_value,lookup_vector,[result_vector])

參數：

● lookup_value必需。LOOKUP在第一個向量中搜索的值。可以是數字、文本、邏輯值、名稱或對值的引用。

● lookup_vector必需。只包含一行或一列的區域。可以是文本、數字或邏輯值。

● result_vector可選。只包含一行或一列的區域。

（1）精確匹配（查找范圍和返回范圍一致）

以人物攻擊力表為例，數據如表2-12所示。字段包括性別、姓名、攻擊力。數據位于區域A1:C7，區域E2:E3是查找值，區域F2:F3是返回值。

公式與步驟：

1）選中區域A1:C7，單擊“數據|排序和篩選|排序”按鈕，在“排序”對話框中勾選“數據包含標題”選項，“主要關鍵字”欄中篩選“姓名”，“次序”欄中篩選“升序”，然后單擊“確定”按鈕。

2）單元格F2內輸入公式“=LOOKUP(E2,$B$2: $B$7)”，然后向下拖拽復制公式，結果如圖2-88所示。

提示：

● 查找范圍array數組中的值必須按升序排列：…, -2, -1, 0, 1, 2, …, A～Z, FALSE, TRUE，否則LOOKUP可能無法返回正確的值；文本不區分大小寫。

● 沙和尚、孫悟空在B列中存在，可以返回精確的數值。

（2）近似匹配（查找范圍和返回范圍一致）

以人物攻擊力表為例，數據如表2-12所示。字段包括性別、姓名、攻擊力。數據位于區域A1:C7，單元格E2是查找值，單元格F2是返回值。

公式與步驟：

1）選中區域A1:C7，單擊“數據|排序和篩選|排序”按鈕，在“排序”對話框中勾選“數據包含標題”選項，“主要關鍵字”欄中篩選“姓名”，“次序”欄中篩選“升序”，然后單擊“確定”按鈕。

2）單元格F2內輸入公式“=LOOKUP(E2,B2:B7)”，結果如圖2-89所示。

提示：

● 查找范圍array數組中的值必須按升序排列：…, -2, -1, 0, 1, 2, …, A～Z, FALSE, TRUE，否則LOOKUP可能無法返回正確的值；文本不區分大小寫。

● 女兒國國王在B列中不存在，返回小于等于查找值的最大值。這里由于女兒國國王在B列里面的按照升序排序的位置在嫦娥仙子的下面（拼音排序法），所以返回嫦娥仙子。

（3）查找范圍和返回范圍不一致

以人物攻擊力表為例，數據如表2-12所示。字段包括性別、姓名、攻擊力。數據位于區域A1:C7，區域E2:E4是查找值，區域F2:G4分別是攻擊力和性別的返回值。

公式與步驟：

1）選中區域A1:C7，單擊“數據|排序和篩選|排序”按鈕，在“排序”對話框中勾選“數據包含標題”選項，“主要關鍵字”欄中篩選“姓名”，“次序”欄中篩選“升序”，然后單擊“確定”按鈕。

2）單元格F2內輸入公式“=LOOKUP(E2,$B$2:$B$7,C$2:C$7)”。

3）單元格G2內輸入公式“=LOOKUP(E2,$B$2:$B$7,A$2:A$7)”。

4）同時選中F2:G2區域，然后向下拖拽復制公式，結果如圖2-90所示。

提示：

● 查找范圍lookup_vector向量中的值必須按升序排列：…, -2, -1, 0, 1, 2, …, A～Z,FALSE, TRUE，否則LOOKUP可能無法返回正確的值；文本不區分大小寫。

● result_vector可以省略。只包含一行或一列的區域。result_vector參數必須與lookup_vector參數大小相同。

● 如果lookup_value在lookup_vector中不存在，返回小于等于查找值的最大值。所以查找女兒國國王對應的數據返回的是嫦娥仙子的攻擊力和性別。

5.MATCH函數

功能說明：在區域內搜索特定的項，然后返回該項在此區域中的相對位置。

語法：MATCH(lookup_value,lookup_array,[match_type])

參數：

● lookup_value必需。要在lookup_array中匹配的值。

● lookup_array必需。要搜索的單元格區域。

● match_type可選。數字-1、0或1。參數的默認值為1。

（1）精確匹配

以某超市的商品信息表為例，數據如表2-13所示。字段包括商品名稱、價格、數量。數據位于區域A1:C6。區域F2:F4是說明，區域G2:G4是計算結果。

公式與步驟：

● 示例一：單元格G2內輸入公式“=MATCH("毛巾",A1:A6,0)”。

● 示例二：單元格G3內輸入公式“=MATCH(19, B1:B6,0)”。

● 示例三：單元格G4內輸入公式“=MATCH(20,C1:C6,0)”，結果如圖2-91所示。

提示：如需精確匹配，參數match_type必須為0。

（2）近似匹配

以某超市的商品信息表為例，數據如表2-13所示。字段包括商品名稱、價格、數量。數據位于區域A1:C6。要求查找數量35在區域C1:C6匹配的位置。

公式：單元格G2內輸入公式“=MATCH(35,C1:C6,1)”，結果如圖2-92所示。

提示：

● 如需近似匹配，參數match_type的值為1或-1。

● match_type省略或1，查找小于或等于lookup_value的最大值。lookup_array參數中的值必須以升序排序，例如，…,-2,-1, 0, 1, 2, …, A～Z, FALSE, TRUE。

● match_type為-1，查找大于或等于lookup_value的最小值。lookup_array參數中的值必須按降序排列，例如，TRUE, FALSE, Z～A, …, 2, 1, 0,-1,-2, …, 等等。

（3）根據日期判斷所屬季度

以某超市的商品銷售表為例，數據如表2-14所示。字段包括日期、商品名稱、數量。要求根據商品銷售日期來判斷當前日期所屬季度。

公式與步驟：單元格D2內輸入公式“=MATCH(MONTH(A2),{1,4,7,10},1)”，然后向下拖拽復制公式，結果如圖2-93所示。

提示：

● 用MONTH函數取出日期對應的月份，然后用MATCH函數進行近似匹配。這里把日期對應的月份在數組{1,4,7,10}里面進行查找。如果可以匹配，就返回當前月份在數組里面的位置。如果月份在數組里面匹配不到，就返回小于等于當前月份的最大值所屬的位置，也就是數組{1,4,7,10}對應的位置。

● 有些月份在數組{1,4,7,10}中并沒有出現，需要近似匹配。數組{1,4,7,10}是以升序進行排序的，參數match_type設置為1。

6.INDEX函數

功能說明：返回表格或區域中的值或值的引用。

語法：INDEX(array,row_num,[column_num])

參數：

● array必需。單元格區域或數組常量。

● row_num必需。選擇數組中的某行，函數從該行返回數值。

● column_num可選。選擇數組中的某列，函數從該列返回數值。

示例：以某企業的銷售業績表為例，數據如表2-15所示。字段包括月份、交單數、業績。數據位于區域A1:C7。要求匹配查找區域A1:A7中第三個位置對應的數值，區域A1:C1中第三個位置對應的數值，區域A1:C7中第三行和第三列交叉處的數值。

公式與步驟：

● 示例一：單元格G2內輸入公式“=INDEX(A1:A7,3)”。

● 示例二：單元格G3內輸入公式“=INDEX(A1:C1,3)”。

● 示例三：單元格G4內輸入公式“=INDEX(A1:C7, 3,3)”，結果如圖2-94所示。

提示：

● 如果array參數僅包含一行或一列，參數row_num或column_num為可選參數。

● 如果array參數中包含多行多列，而且僅使用了row_num或column_num一個參數，函數結果則返回數組中的整行或整列。

● 如果array參數中包含多行多列，而且同時使用了row_num和column_num參數，函數結果則返回某一行和某一列的交叉單元格中的值。

7.OFFSET函數

功能說明：返回對單元格或單元格區域中指定行數和列數的區域的引用。返回的引用可以是單個單元格或單元格區域。

語法：OFFSET(reference,rows,cols,[height],[width])

參數：

● reference必需。作為偏移基準的參照。

● rows必需。需要左上角單元格引用的向上或向下行數。

● cols可選。需要結果的左上角單元格引用的從左到右的列數。

● height可選。需要返回的引用的行高。

● width可選。需要返回的引用的列寬。

（1）查找并返回某一個單元格數值

以某企業的產品銷售業績表為例，數據如表2-16所示。字段包括省份、電器、服裝、日用品。數據位于區域A1:D5。要求以單元格A1作為引用的起始位置，查找并返回浙江省的服裝數值。

公式與步驟：單元格G2內輸入公式“=OFFSET (A1,2,2,1,1)”，結果如圖2-95所示。

提示：如果返回的是某一個單元格的數值，參數height和width可以省略。

（2）查找并返回單元格區域數值

以某企業的產品銷售業績表為例，數據如表2-16所示。字段包括省份、電器、服裝、日用品。數據位于區域A1:D5。要求以單元格A1作為查找引用的起始位置，查找并返回“上海”的電器、服裝、日用品數值。

公式與步驟：

1）選中區域G2:I2，在單元格G2內輸入公式“=OFFSET(A1,3,1,1,3)”。

2）按〈Ctrl+Shift+Enter〉組合鍵創建數組公式，結果如圖2-96所示。

8.INDIRECT函數

功能說明：返回由文本字符串指定的引用。此函數立即對引用進行計算，并顯示其內容。

語法：INDIRECT(ref_text,[a1])

參數：

● ref_text必需。對單元格的引用，此單元格包含A1樣式的引用、R1C1樣式的引用、定義為引用的名稱或對作為文本字符串的單元格的引用。

● [a1]可選。邏輯值，用于指定在ref_text中的引用的類型。參數值為TRUE指定的是A1樣式，參數值為FALSE指定R1C1引用樣式。

（1）查找返回指定單元格數值

以某門店到訪用戶人數表為例，數據如表2-17所示。字段包括門店名稱、到訪用戶數。數據位于區域A1:B5。要求查找返回單元格B2、B3以及B4中的引用值。區域D2:D4是INDIRECT函數公式，區域E2:E4是說明，區域F2:F4是計算結果。

公式與步驟：

● 示例一：單元格F2內輸入公式“=INDIRECT("B2", TRUE)”。

● 示例二：單元格F3內輸入公式“=INDIRECT("R3C2",0)”。

● 示例三：單元格F4內輸入公式“=INDIRECT("B"&ROW(B4),1)”，結果如圖2-97所示。

提示：

● 參數ref_text一定是引用樣式對應的文本類型。

● 如果參數[a1]為TRUE，ref_text采用A1樣式；如果參數[a1]為FALSE，ref_text采用R1C1樣式。

（2）多個工作表引用合并數據

工作簿中的5個工作表，工作表的名稱分別為“union”“st_001”“st002”“st_003”“st_004”，每個工作表里區域A1:A5分別是姓名、性別、年齡、班級、成績，區域B1:B5分別是各自對應的數值，數據如圖2-98所示。要求把4個工作表的學生信息合并到“union”工作表中。

公式與步驟：單元格B2內輸入公式“=INDIRECT($A2&"!B"&COLUMN(A1),1)”，然后向下向右拖拽復制公式，結果如圖2-99所示。

提示：

●“union”工作表內區域A2:A5的數值分別對應4個工作表的名稱，因此可以用單元格引用、固定字符的連接拼湊到對應數值所在工作表內的位置，拼接的位置為“$A2&"!B"&COLUMN(A1)”，此時單元格A2是列絕對引用。

● 公式里面的參數ref_text采用的是A1樣式，因此參數[a1]數值為1。

以上是對匹配查找函數的介紹，并采用示例對函數進行了功能講解，下面的匹配查找函數案例一～案例二是匹配查找函數的應用擴展，對于同一個實例采用了多種方法來解決。

9.匹配查找函數案例一

案例說明：以不同區域的城市對照數據為例，數據如表2-18所示。數據位于單元格區域A1:B13，單元格區域E3:E6為部分城市，要求根據城市信息匹配城市所屬區域。

公式與步驟：

● 方法一：

1）復制A列數據到C列作為輔助列。

2）單元格F3內輸入公式“=VLOOKUP(E3,B:C,2,0)”，然后向下拖拽復制公式。

● 方法二：單元格G3內輸入公式“=VLOOKUP(E3,IF({1,0},$B$2:$B$13,$A$2:$A$13), 2,0)”，然后向下拖拽復制公式。

● 方法三：單元格H3內輸入公式“=INDEX($A$1:$B$13,MATCH(E3,$B$1:$B$13, 0),1)”，然后向下拖拽復制公式。

● 方法四：

1）選中單元格區域A1:C13，單擊“數據|排序和篩選|排序”命令，在“排序”對話框中“主要關鍵字”欄篩選“城市”，“次序”欄篩選“升序”，然后單擊“確定”按鈕。

2）單元格I3內輸入公式“=LOOKUP(E3,$B$2:$B$13,$A$2:$A$13)”，然后向下拖拽復制公式。

● 方法五：

1）選中單元格區域A1:C13，單擊“數據|排序和篩選|排序”命令，在“排序”對話框中“主要關鍵字”欄篩選“城市”，“次序”欄篩選“升序”，然后單擊“確定”按鈕。

2）單元格J3內輸入公式“=LOOKUP(1,1/(E3=$B$2:$B$13),$A$2:$A$13)”，然后向下拖拽復制公式，結果如圖2-100所示。

10.匹配查找函數案例二

案例說明：以某超市的商品價格變動表和商品出庫價目表為例，當商品價格發生變化時會標注對應日期下的價格，其余日期下商品價格不變。要求根據左側的商品價格變動表填充右側商品出庫價目表，數據如圖2-101所示。

公式與步驟：

● 方法一：

1）選中單元格區域A2:F8，按組合鍵〈F5〉或〈Ctrl+G〉組合鍵，在“定位”對話框中單擊“定位條件”按鈕，在“定位條件”對話框中選擇“空值”，然后單擊“確定”按鈕，定位所選區域的空值單元格。

2）按順序單擊鍵盤上的“=”“←”鍵，然后按下〈Ctrl+Enter〉組合鍵，填充空值單元格區域。

3）單元格J4內輸入公式

“=INDEX($A$2:$F$8,MATCH(I4,$A$2:$A$8,0),MATCH(H4,$A$2:$F$2,1))”，然后向下拖拽復制公式。

● 方法二：

1）填充空值單元格方法同上述方法一的步驟1和步驟2。

2）單元格K4內輸入公式“=VLOOKUP(I4,$A$2:$F$8,MATCH(H4,$A$2:$F$2,1),0)”，然后向下拖拽復制公式。商品最新單價的匹配查找結果如圖2-102所示。