標籤：

Excel 逆向查詢的 6 種方法

02-02

VLOOKUP 是大家最常用的查詢函數，一般也是帶領大家進入 Excel 函數大門的「啟蒙老師」。不過它並不是萬能的，有些時候它也有做不到的事情。咱們先回顧一下 VLOOKUP 的語法。

反人類版的語法：

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

地球人能看懂的語法：

VLOOKUP (第 1 參數，第 2 參數，第 3 參數，[可有可無的第 4 參數])

語法解析：

第 1 參數：找什麼（查找值）

第 2 參數：哪裡找（查找區域）

第 3 參數：第幾列（返回值在查找區域的第幾列）

第 4 參數：精確還是模糊（0 或 1，默認為 1）

在使用 VLOOKUP 的時候，對於第 2 個參數（即查找值與返回值所在的單元格區域），查找值所在列必須在返回值所在列之前，不然函數無法工作。

如下圖所示，如果需要查詢鍵盤對應的存貨編碼，則需要將 A、B 兩列進行對換，即存貨名稱這一列必須在存貨編碼這一列的前面！那麼，圖中的情況下，該怎麼查詢呢？每次都要將兩列數據進行互換嗎？

當然不是！我們有 6 種解決方法！

1. VLOOKUP + IF

=VLOOKUP(E3,IF({1,0},B2:B10,A2:A10),2,0)

另外 3 個參數不難理解，核心在於用 IF 函數和數組公式構造的第 2 參數。簡單的說，就是利用 IF 函數，值為 1 即 TRUE 的時候返回 B 列單元格，值為 0 即 FALSE 的時候返回 A 列單元格， 重新構造查找區域，也就是 B 列存貨名稱在前、A 列存貨編碼在後的新單元格區域。這個時候， VLOOKUP 函數就可以正常查找了。

以後將會開篇詳細說明 IF 和 {1,0} 的使用方法哦，敬請期待。

2. VLOOKUP + CHOOSE

=VLOOKUP(E3,CHOOSE({1,2},B2:B10,A2:A10),2,0)

這裡就是將 IF 函數替換為 CHOOSE 函數，效果是一樣的，也就不再特別說明了。注意哦，這裡不是 {1,0} 而是 {1,2}。

3. LOOKUP

=LOOKUP(1,0/(E3=B2:B10),A2:A10)

這裡就先不作解釋了，不然篇幅太長，以後開篇單獨介紹 LOOKUP 函數的用法。這裡童鞋們只要將裡面的單元格替換，就能變成自己的公式啦。

4. INDEX + MATCH

=INDEX(A2:A10,MATCH(E3,B2:B10,0))

簡單說明一下，MATCH 函數也是查找函數，但是它返回的不是單元格的值，而是行號或者列號。比如 MATCH(「顯卡」,B2:B10,0)，返回的是 7 這個數值，也就是說 顯卡 在 B2:B10 的第 7 行這個位置。而 INDEX 函數的作用是，返回 A2:A10 這個區域第幾行的內容，例如 INDEX(A2:A10,7) ，就會返回 A2:A10 這個區域的第 7 行內容，也就是 B0007 這個值。

通過將這兩個函數組合，MATCH 函數返回行號作為INDEX 的第 2 參數，INDEX 函數返回單元格區域對應行號的值，兩個函數完美搭配，實現反向查詢！

5. OFFSET + MATCH

=OFFSET(A1,MATCH(E3,B2:B10,0),)

OFFSET 函數是將基點單元格進行移動的函數，可以返回移動後單元格的值。它有 3 個必填參數：

  1. 基點單元格
  2. 移動的行數：向下移動為正數，向上移動為負數
  3. 移動的列數：向右移動為正數，向左移動為負數

注意到上面公式里最後一個， 嗎，因為這裡用不到第 3 參數，但是它又是必填的，所以這裡只能填寫一個逗號作為代替。

拿查找 滑鼠 為例，它在 B2:B10 的第 4 個位置，利用 MATCH 函數即可返回這個值。然後利用 OFFSET 的位移功能，將 A1 向下移動 4 個單元格即 A5 ，是不是返回滑鼠對應的存貨編碼了呢。

6. INDIRECT + MATCH

=INDIRECT("A"&MATCH(E3,B2:B10,0)+1)

INDIRECT 函數的作用是將文本轉換為真正可以使用的公式。比如在單元格里輸入 "=A8"，回車之後你只能看到 =A8 這幾個字元，而不是 A8 這個單元格的內容 B0007。

但是INDIRECT 函數就可以做到。

咱們又換一個查詢內容，這次就用 手機 吧。通過 MATCH 函數返回手機在 B2:B10 的第 2 個位置，也就是 B3 單元格，行號是 3，所以這裡需要 +1。對應的存貨編碼也是在第 3 行，所以咱們只要構造出 A3 就可以得到手機對應的存貨編碼了。這個時候，把這些字元往 INDIRECT 函數裡面丟進去就行啦，最後類似於 INDERECT("A"&2+1) 這樣的存在。回車之後是不是返回正確的值了呢？大家可以驗證一下。

上面這 6 種方法童鞋們是不是的掌握了呢。如果有不理解的地方可以在留言里諮詢哦，也可以前往各大搜索引擎搜索哈。以後我也會開篇講解各個函數，大家繼續關注哦。

還有示例文件這裡下載哦，大家練練手吧~

pan.baidu.com/s/10Vda3Z

— THE END —

關注微信公眾號Excel全攻略（微信號：ExcelSkills），發現更多精彩內容……


推薦閱讀：

TAG:Excel技巧 | MicrosoftExcel |