|
一、語法和參數(shù) 1. 語法： =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 語法解釋：=XLOOKUP(查找值,查找數(shù)組,返回?cái)?shù)組,未找到值,匹配模式,搜索模式) 2. 參數(shù) 1. 必需參數(shù)三個(gè)： ① lookup_value，要搜索的值； ② lookup_array，要搜索的區(qū)域或數(shù)組； ③ return_array，要返回的區(qū)域或數(shù)組。 2. 可選參數(shù)三個(gè)： ① [if_not_found]，找不到匹配值； 返回指定參數(shù)[if_not_found]； 如果未指定參數(shù)，則顯示#N/A； ② [match_mode]，指定匹配類型； 0 未找到匹配值，則顯示#N/A -1 未找到匹配值，則返回較小值 1 未找到匹配值，則返回較大值 2 通匹符 ③ [search_mode]，指定搜索模式。 1 從第一項(xiàng)開始搜索 -1 從最后一項(xiàng)開始搜索 2 按升序搜索 -2 按降序搜索 三、函數(shù)示例
1. 縱向查找
以下圖表格為例，根據(jù)姓名查找得分?？梢暂斎牍剑?/span>
=XLOOKUP(D2,A2:A11,B2:B11) 整個(gè)公式的含義是，使用XLOOKUP函數(shù)，查找“林沖”在姓名列的位置，并返回得分列的相應(yīng)位置對應(yīng)的得分。 如果使用VLOOKUP函數(shù)的話，公式為： =VLOOKUP(D2,A2:B11,2,0) 2. 橫向查找 以下圖表格為例，姓名行在上面，得分行在下面。根據(jù)姓名查找得分?？梢暂斎牍剑?/span> =XLOOKUP(A6,A1:K1,A2:K2) 公式說明：查找值H2，查找區(qū)域?yàn)锽1:E1，返回區(qū)域?yàn)锽2:E2。 VLOOKUP函數(shù)不能橫向查找，如果使用HLOOKUP函數(shù)的話，公式為： =HLOOKUP(A6,B1:K2,2,0) 3. 逆向查找
如果需要查找的目標(biāo)值在左邊，需要進(jìn)行逆向查找。如下圖，姓名列在右邊，得分列在左邊。根據(jù)姓名從右向左查找得分。單元格G2，輸入公式： =XLOOKUP(F2,C2:C5,B2:B5) 公式說明：查找值F2，查找區(qū)域?yàn)镃2:C5，返回區(qū)域?yàn)锽2:B5。 使用VLOOKUP函數(shù)也能實(shí)現(xiàn)逆向查找，但是比較復(fù)雜，公式如下： =VLOOKUP(E2,IF({1,0},$C$2:$C$11,$B$2:$B$11),2,0) 4. 查詢失敗匹配值 如果查詢的結(jié)果沒有匹配值，查詢失敗默認(rèn)顯示“#N/A”。如果給公式加上第四個(gè)參數(shù)：匹配值，則查詢失敗會(huì)顯示匹配值。 如下圖，要根據(jù)姓名，查找得分，輸入公式： 公式1：=XLOOKUP(D3,A3:A12,B3:B12) 公式說明：查找值F2，查找區(qū)域?yàn)?span id="bh51tjlzh" class="cye-lm-tag">A3:A12，返回區(qū)域?yàn)?span id="bh51tjlzh" class="cye-lm-tag">B3:B12。這個(gè)公式因未指定第四個(gè)參數(shù)，則默認(rèn)顯示“#N/A”。 公式2：=XLOOKUP(D3,A3:A12,B3:B12,'無')
公式說明：查找值D3，查找區(qū)域?yàn)?span id="bh51tjlzh" class="cye-lm-tag">A3:A12，返回區(qū)域?yàn)?span id="bh51tjlzh" class="cye-lm-tag">B3:B12，未找到匹配值 則顯示“無”。 5. 區(qū)間查找
如下圖，要根據(jù)【分值】區(qū)間，查找【積分】。輸入公式，向下填充： =XLOOKUP(D3,(ROW($A$1:$A$11)-1)*10,$B$3:$B$13,,1) 公式說明：查找值D3，查找區(qū)域?yàn)?ROW($A$1:$A$11)-1)*10（構(gòu)建數(shù)組），返回區(qū)域?yàn)?B$3:$B$13，指定匹配類型1；
注意：查找區(qū)域使用函數(shù)嵌套構(gòu)建數(shù)組，指定匹配類型1（即未找到匹配值，則返回較大值）； 5. 指定搜索模式（有重復(fù)值的數(shù)據(jù)查找） 我們可以指定查找模式：從第一項(xiàng)往后查找、從最后往前查找。 如下圖，要根據(jù)姓名，查找得分，這里出現(xiàn)了相同值。輸入公式： =XLOOKUP(E2,A2:A5,B2:B5,,,-1) 公式說明：查找值E2，查找區(qū)域?yàn)锳2:A5，返回區(qū)域?yàn)锽2:B5，搜索模式為-1。（因?yàn)檫@個(gè)參數(shù)是第六參數(shù)，所以，需要加多2個(gè)逗號“，”；這個(gè)能理解嗎？） 如果不輸入任何參數(shù)的話，默認(rèn)是從第一項(xiàng)開始往后查找，結(jié)果就是94。 6. 交叉查找 如下圖，要根據(jù)姓名和季度，查找交叉值。輸入公式，向右填充： =XLOOKUP(H2,$B$2:$E$2,XLOOKUP($G3,$A$3:$A$12,$B$3:$E$12))
公式說明：查找值H2，查找區(qū)域?yàn)?B$2:$E$2，返回區(qū)域?yàn)閄LOOKUP($G2,$A$3:$A$12,$B$3:$E$12)。 注意：因?yàn)樾枰蛴姨畛?，所以要注意參?shù)的相對和絕對引用； 以上就是，XLOOKUP函數(shù)的常用的幾種用法，有興趣的朋友可以進(jìn)一步研究。
|
