視頻鏈接
一、Xlookup函數
1.Xlookup函數語法
語法：
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], , ) =Xlookup（值,查找列,返回值列,找不到顯示,匹配類型,搜索模式）
參數說明：
lookup_value：要查找的值，可以是文本、數字、日期等。
lookup_array：查找的範圍。XLOOKUP 會在此範圍中查找 lookup_value。
return_array：當找到匹配值時，要返回的值的範圍。它的大小應與 lookup_array 相同。
[if_not_found]（可選）：如果沒有找到匹配項時要返回的值。如果省略此參數，當未找到時將返回 #N/A 錯誤。
（可選）：指定查找類型的匹配方式。
0：精確匹配（默認）。
-1：查找小於或等於 lookup_value 的最大值。
1：查找大於或等於 lookup_value 的最小值。
2：通配符匹配（支持 * 和 ?）。
（可選）：指定查找的方向。
1：從第一個元素到最後一個元素（默認）。
-1：從最後一個元素到第一個元素（反向查找）。
2.Xlookup函數應用
（1）單條件查找
語法：
= XLOOKUP(查找值，查找列，返回值列）
！注意事項：
用法類似LOOKUP函數，但注意使用LOOKUP函數查找區域需要升序排列
也可以使用VLOOKUP函數，需要注意查找值所在的列必須是在查找區域的最左列
（2）反向查找
語法：
= XLOOKUP(查找值，查找列，返回值列）
示例：根據演員張國榮，查找主演電影
！注意事項：
XLOOKUP可以反向查找，VLOOKUP常規下不可以反向查找會出錯
（3）多條件查找
語法：
= XLOOKUP(條件1&條件2，條件1區域&條件2區域，返回值區域)
示例：查找導演克里斯托弗·諾蘭在2010年上映的電影
！注意事項：
多條件查找也可以使用LOOKUP函數
= LOOKUP(1,0/(條件1*條件2*條件3...條件n),返回列）
（4）查找不到顯示另外值
語法：
= XLOOKUP(查找值，查找列，返回值區域，找不到顯示)
示例：查找張藝謀導演的主導的電影
！注意事項：
查不到顯示另外值，如果使用VLOOKUP函數需要結合IFERROR函數
二、Match與Index函數
1.函數語法
（1）Match
語法：
MATCH(lookup_value, lookup_array, )
參數說明：
lookup_value：要查找的值，可以是數字、文本、日期等。這個值會在 lookup_array 中進行查找。
lookup_array：要搜索的範圍或數組。MATCH 會在此範圍中查找 lookup_value。
（可選）：指定查找類型的匹配方式。
1 或省略（默認）：查找小於或等於 lookup_value 的最大值。lookup_array 必須按升序排列（從小到大）。
0：精確匹配。lookup_array 可以是任何順序。
-1：查找大於或等於 lookup_value 的最小值。lookup_array 必須按降序排列（從大到小）。
返回值：
MATCH 返回值在 lookup_array 中的相對位置。如果沒有找到匹配值，MATCH 會返回 #N/A 錯誤。
示例：
假設我們有以下數據：
示例1：查找李四的位置
我們想要查找 "李四" 在數組中的位置：
=MATCH("李四", A2:A5, 0)
解釋：
lookup_value 是 "李四"。
lookup_array 是 A2:A5（姓名列）。
match_type 是 0，表示精確匹配。
結果： 該公式返回 2，因為 "李四" 位於第二行。
示例2：查找大於或等於某個值的最小值的位置
假設我們有以下一列數字，且按升序排列：
我們想查找小於或等於 25 的最大值的位置：
=MATCH(25, B2:B5, 1)
解釋：
lookup_value 是 25。
lookup_array 是 B2:B5（數字列）。
match_type 是 1，表示查找小於或等於 lookup_value 的最大值。
結果： 該公式返回 2，因為 20 是小於 25 的最大值，它在第二個位置。
示例3：查找文本中的匹配
如果我們想查找某個文本項（如 "張三"）的索引：
=MATCH("張三", A2:A5, 0)
結果： 該公式返回 1，因為 "張三" 是該數組中的第一個元素。
（2）Index
語法：
INDEX(array, row_num, )
參數說明：
array：要從中獲取數據的數組或範圍。這個參數包含了所有可能的數據值。
row_num：指定返回值的行號。如果為 0，則表示返回整個列的數組。
（可選）：指定返回值的列號。如果不提供 column_num，則假定數組只有一列。
返回值：
INDEX 返回 array 中指定位置的值。根據提供的 row_num 和 column_num 參數，INDEX 會在相應位置返回值。
示例：
假設我們有以下數據表：
示例1：從單列中返回值
假設我們想從 姓名 列（A列）返回第2行的值（"李四"）：
=INDEX(A2:A5, 2)
解釋：
array 是 A2:A5，包含所有姓名。
row_num 是 2，表示我們需要返回第2行的值。
結果： 該公式返回 "李四"。
示例2：從多列中返回值
假設我們想從表格中返回第3行、第2列的值（即 "35" 對應年齡的值）：
=INDEX(A2:C5, 3, 2)
解釋：
array 是 A2:C5，包含所有數據。
row_num 是 3，表示我們要查找第3行。
column_num 是 2，表示我們要查找第2列的值，即 "年齡" 列。
結果： 該公式返回 35，因為 "王五" 的年齡是35。
示例3：返回整行或整列
如果我們想返回整個 "年齡" 列的數據，可以這樣寫：
=INDEX(B2:B5, 0)
解釋：
array 是 B2:B5，包含所有年齡數據。
row_num 是 0，表示返回整個列的數據。
結果： 該公式返回整個 "年齡" 列的數據：25, 30, 35, 40。
示例4：與 MATCH 函數結合使用
INDEX 和 MATCH 常常結合使用，來查找一個值的位置，並根據該位置返回另一個相關值。假設我們想找出 "王五" 的性別：
=INDEX(C2:C5, MATCH("王五", A2:A5, 0))
解釋：
MATCH("王五", A2:A5, 0) 查找 "王五" 在 A2:A5 中的位置，結果為 3。
INDEX(C2:C5, 3) 查找 C2:C5 中第3行的值，即 "男"。
結果： 該公式返回 "男"，因為 "王五" 的性別是 "男"。
實例
從單個區域中獲取值
=INDEX(A1:C3, 2, 3)
這個公式將返回區域 A1:C3 中第二行第三列的值。
當行號為 0 時，返回整列的值
=INDEX(A1:C3, 0, 2)
這個公式返回區域 A1:C3 中第二列的所有值。
兩者一般嵌套使用
2.認識column函數
（1）COLUMN函數
語法：
COLUMN([reference])
參數說明：
reference（可選）：這是一個單元格或區域的引用。如果省略此參數，COLUMN 函數將返回包含公式的單元格所在的列號。
返回值：
如果指定了 reference，COLUMN 返回該引用所在列的列號。
如果沒有指定 reference，則返回包含公式的單元格所在的列號。
示例：
示例1：基本用法
假設我們想知道單元格 B1 所在的列號：
=COLUMN(B1)
解釋：
B1 是我們要查詢的單元格。
COLUMN 函數返回 B 列的列號，即 2。
結果： 該公式返回 2，因為列 B 是第二列。
示例2：在沒有引用時使用
如果你在 C5 單元格中使用 COLUMN 函數，而沒有指定任何單元格引用：
=COLUMN()
解釋：
因為公式位於 C5，COLUMN 函數返回 C 列的列號，即 3。
結果： 該公式返回 3，因為 C 列是第三列。
示例3：返回一個區域的列號
假設我們想知道區域 A1:C5 的第一個單元格（A1）所在的列號：
=COLUMN(A1:C5)
解釋：
A1:C5 是一個區域。
COLUMN 函數返回區域第一個單元格 A1 所在列的列號，即 1。
結果： 該公式返回 1，因為 A 列是第一列。
示例4：返回多個列號
如果我們在一個公式中使用一個區域，例如 A1:B5，並希望返回這些列的列號，我們可以使用 COLUMN 函數：
=COLUMN(A1:B5)
解釋：
COLUMN 函數返回區域內所有列的列號。
由於區域 A1:B5 包含 A 列和 B 列，所以它會返回兩個列號：1 和 2。
結果： 該公式返回 {1, 2}，表示 A 列和 B 列。
示例5：與其他函數結合使用
你可以將 COLUMN 函數與其他函數結合使用。例如，如果你希望根據某個單元格的列號計算距離，可以結合 COLUMN 與 INDIRECT 函數：
=COLUMN(INDIRECT("C1"))
解釋：
INDIRECT("C1") 返回 C1 單元格的引用。
COLUMN 函數返回 C1 所在的列號，即 3。
結果： 該公式返回 3，因為 C1 在 C 列。
實例：
說明：①可獲取單元格或單元格區域的列序號。reference引用為單元格時返回結果是單元格對應的列序號。比如輸入“=COLUMN(C13)”輸出結果為3。②reference 不能引用多個區域。當引用連續幾列時，輸出結果溢出。比如輸入“=COLUMN(C:D)”輸出結果是兩個單元格3和4
（2）COLUMNS函數
語法：
COLUMNS(array)
參數說明：
array：這是你要計算列數的單元格區域。你可以指定一個單元格區域、一個行列組合的區域，或者一個數組。
返回值：
返回指定區域中包含的列數。
示例：
示例1：基本用法
假設我們想知道區域 A1:C5 中包含多少列：
=COLUMNS(A1:C5)
解釋：
區域 A1:C5 包括 A 列、B 列和 C 列。
COLUMNS 函數將返回該區域的列數。
結果： 該公式返回 3，因為區域 A1:C5 包含 3 列（A、B、C 列）。
示例2：單行區域
如果你想計算一個單行區域中有多少列，假設區域是 A1:Z1，你可以使用：
=COLUMNS(A1:Z1)
解釋：
區域 A1:Z1 包括從 A 列到 Z 列的一整行。
COLUMNS 函數將返回該區域的列數。
結果： 該公式返回 26，因為區域 A1:Z1 包含 26 列（從 A 到 Z）。
示例3：計算多個行和列的區域
如果你有一個區域 A1:D10，並想知道該區域有多少列，可以使用：
=COLUMNS(A1:D10)
解釋：
區域 A1:D10 包括 A 列、B 列、C 列和 D 列。
COLUMNS 函數將返回該區域的列數。
結果： 該公式返回 4，因為區域 A1:D10 包含 4 列（A、B、C、D 列）。
示例4：結合其他函數使用
你可以將 COLUMNS 函數與其他函數結合使用。例如，如果你想計算某個區域動態變化時的列數，可以結合 OFFSET 函數：
=COLUMNS(OFFSET(A1, 0, 0, 5, 3))
解釋：
OFFSET(A1, 0, 0, 5, 3) 返回一個從 A1 開始的區域，包含 5 行和 3 列。
COLUMNS 函數返回該區域中的列數。
結果： 該公式返回 3，因為 OFFSET 函數返回了 3 列的區域。
實例
說明：①引用單元格或者某列單元格區域時，輸出結果為1。如輸入“=COLUMNS(A13)”或“=COLUMNS(D:D)”，輸出結果均為1。② 引用多列單元格區域時返回結果為列個數。如輸入“=COLUMNS(A1:C12)”，涉及到A、B、C三列，所以輸出結果為3。
（3）COLUMN函數與COLUMNS函數的區別
COLUMN 函數用於返回單元格或區域的 列號（數字），它幫助你知道一個單元格處於第幾列。
COLUMNS 函數用於返回一個 區域中包含的列數。它告訴你一個指定區域有多少列。
即
COLUMN 返回列的 數字編號，而 COLUMNS 返回列的 數量（即區域中的列數）。
（4）COLUMN函數與VLOOKUP函數結合使用
源數據：
通過“客戶ID”得出其它數據，在返回列的部分使用column公式
注：源數據表頭和要查詢數據表頭一致，不一致可以先調換順序，再查找