Excel經典公式嵌套（XLOOKUP+INDEX+FIND+MATCH+ISNUMBER）關鍵字匹配經典案例！

大家好 這一張呢給大家分享一個比較有意思的案例啊 這個案例呢也是來自於一個粉絲同學的提問 他的問題呢是這個樣子的 這邊呢會有一張運費表格 比如說某一些商品 他的運費標準是0到5公斤 運送到各個省份 他所對應到的這個單價 那這個呢是5到10公斤 下方的是10到15公斤 好 那現在我想要去找到這個最後的總價 應該如何去進行計算 首先這個位置呢 如果它是區間就一定會牽涉到會用到近似匹配 那並且呢 在這個過程中間我們還會遇到地址啊 注意在這一列當中呢 它會有一些地址 它是屬於山東這個位置呢 它是屬於四川前面和後面的會有一些其他的具體的這個文字明細啊 所以我們應該如何來進行一個價格的計算呢 好 大家先跟上我的思路 我們先講完 然後呢 最後再去做一個總結 首先既然會要用到近似匹配 那必然我們需要把這個重量這個部分的去做一個分解 這邊可以來寫到零 下方可以來寫到5 下方呢 可以懇求加上1去進行一個智能填充 當然你可以使用到函數去做了啊 好 你看啊 0到5公斤 然後呢 他所對應到的單價是來自於這一行 然後呢 5到10公斤 他是來自於這一行 所以其實這個位置呢 我們所用到的函數也就是index函數加上match函數的一個近似匹配 包括這邊還有一個關鍵字的查找的部分的融合啊 所以大家看好啊 首先我可以來寫到等於index函數 範圍呢 他一定是來自於整個啊 這個價格明細範圍當中 對吧 好 然後呢 我們再來做match match哪個呢 match重量對嗎 首先在index函數中間的第二個函數 我們所找到的是行的位置 所以我們可以來match當前的重量 在哪個位置match呢 在重量這一列當中呢 去進行一個match F4絕對用這個去由於我們所做的是近似匹配啊 所以直接可以來給到括弧就不需要在後面的再去補充零了 好 這個呢 我們就可以確定他所在的行爲 對嗎 那比如說我現在呢 如果直接給到確定 那你可以看到他的一個輸出結果啊 我到這邊先給上一個多號啊 那兩公斤對吧 他其實所對應到的呢 也就是兩公斤在零到五公斤之內對吧 所以呢 他其實是取得這一行的內容 那如果我把這邊呢 把它給變到是六公斤的話呢 你看他就會變成是第二行的數據對吧 好 你再看啊 接下來呢 我們還需要去找到 這些地址對吧 這個地址呢 前面和後面會有一些其他相關的文字 所以啊 這個位置我們必然的會要去做到一個類似於關鍵字的查找之類的啊 所以其實呢 如果他這邊是山東 然後剛纔在那我們已經可以給到這個行爲了嗎 在這一行當中 我們可以找到啊 這個山東他所出現的類位呢 就可以把他的單價可以把它給調取過來 對吧 所以問題就是我們應該如何去做 首先呢 我在旁邊先把這個函數給大家去做分解啊 可以來寫到等於fine的函數 fine的哪個呢 fine的我們的整個這一行當中 F4絕對用它 然後呢 在當前的這個單元格當中呢 去進行fine 你看啊 山東他會出現在第一個位置 因爲我們是fine的中間的每一個地址 然後呢 在當前的這個單元格當中去進行fine的 你想如果我們可以用山東 你比如說這個位置 如果是fine的山東在剛纔的這個文字框當中 他所出現的位置呢 就是第三個位置 對吧 後面他都是value錯誤 就證明說後面他不存在有內容呢 是屬於這個單元格當中內容 所以呢 我們只要找到他但凡會出現數值的位置 比如說我把這個位置把他給換成是雲南 你看雲南呢 他就會出現在第二個位置 所以我要的就是具體這個數值 他所出現的位置 所以接下來呢 我們可以來做一個判斷啊 it's number 當他爲數值則爲true 當他不爲數值的話呢 那則爲false嘛 value錯誤的部分 他都會變成false 所以其實我想要找到就是這個true 他所出現的位置 所以你可以再來做一個match match true 然後呢 在剛纔的這個內容當中呢 去進行find 零精確匹配 括弧回來確定 那這個時候雲南他就出現在整個剛纔我們所選擇這個範圍當中的第二個位置 對吧 那如果現在我把湖北呢 把他給關過來 大家自己可以看一下 湖北呢 他就會出現在第三個位置 所以這個樣子的話 我們已經有了這一行他的具體內容 然後再給到一個具體的列位就可以 所以得到最後的結論 對嗎 所以呢 這個時候呢 你可以把這個公式呢 去做一個複製 然後呢 選擇把他給貼在 列他所在的位置 然後給到回車啊 這個就是我們所得到的單價了 好得到這個單價之後呢 那接下來 用單價呢 然後再去乘以對吧 前面的這個重量的話呢 就等於他的整個的價格 然後再去做一個雙擊下拉 這個就是我們最後的結論 在這個函數當中呢 不適合用vlookup函數去做啊 如果你硬要去做的話呢 可以去使用到hlookup函數 或者能使用到lookup函數 那這個位置呢 我給大家講另外一個延伸的啊 也就是xlookup函數應該如何來做 好 那首先我們可以先看一下 xlookup函數的他的基本用法 比如說我現在把這邊的改到是10啊 然後呢 選擇等於xlookup函數查找當前的重量 在這一列當中去進行查找 返回湖北 他所對應到的呢 就是這一列對嗎 然後呢 給到回撤 那這個樣子呢 我們可以查找到10 他所對應到的呢 就是4 這個他是屬於精確匹配 如果你要去做近似匹配呢 你需要去跳過參數 來到這個叫做matchmode的這個位置呢 選擇到-1 然後呢 選擇給到確定 那比如說我現在給到11 那11呢 他應該是趨於10到15之間 所以他會給到的值呢 也就是給到會4 好 那我們可以先來想一下 這個中間他的變量是什麼 首先我們需要去查找的這個重量呢 他是不變的 往下拉會變成是對應的重量 對嗎 然後呢 需要去查找的這個數值範圍呢 來自於重量這一列當中 去做近似匹配 所以這一列他也不會發生變化 那這個-1呢 他也不會發生變化 所以他的變化呢 其實是來自於這個範圍當中 現在他是湖北 所以是來自於這一列 如果他現在變成是貴州的話呢 就應該是在這一列 所以我們如何把這個範圍 把它給變成是一個活動範圍呢 我在旁邊呢 給大家來寫這個原理呢 跟之前我們所講過的這個原理是一樣的 所以呢 首先我們可以先來做Find函數 來Find這個表頭 F4決定用它 在當前的這個單元格中間呢 去進行Find 括弧回來確定 所以我們所得到的結論呢 也就是之前我們所描述的結果嘛 所以在他的前方呢 可以加上一個IsNumber函數 選擇確定 確定 好 這個時候呢 你看我們就可以得到to或者是force 我們找到to他所在的一個次序位啊 所以呢 我們可以來Match Match什麼呢 Match這個to了 好 然後呢 在剛纔的這個範圍當中 去進行Match 0精確匹配 括弧回來確定 就可以找到是3 對嗎 也就是在整個表頭當中的第三個位置呢 爲湖北 好 那接下來我需要把這個湖北呢 他所對應到的這一列數據呢 把它給套取出來嘛 所以呢 可以來寫到index函數 選擇到下方的範圍當中 F4決定用它 跳過row 給直接給到colon 那這個樣子呢 我們所得到的就是個靈活數據了 對嗎 選擇確定 確定 你看那現在呢 我們所得到的結論也就是這個 呃湖北呢 他所對應到的這個範圍 那如果現在我把它給更換成爲是上海呢 就一定是上海呢 他所對應到的範圍 對嗎 所以我們可以把這個數據呢 然後對他去做一個複製 然後直接把它給放在剛纔的xlookup函數中間去替換 剛纔我們所描述的那個變換區間當中 然後呢 這個位置我們同樣需要去做絕對引用了 好吧 確定 然後呢 再去做一個雙擊下拉 那這個樣子呢 我們可以得到同樣的結論啊 當然需要在他的結論之上呢 再去乘以前方的這個重量確定 然後呢 再去做一個雙擊下拉