大多數Excel用戶需要在工作簿中查找數據。但是最好的查找方法是什么？
Excel提供了兩種主要的查找方法： VLOOKUP和 INDEX – MATCH。盡管兩種方法相似，但INDEX-MATCH的功能更強大。但是，我懷疑VLOOKUP的知名度更高，使用更廣泛。可能是因為如果您要查找某些內容，則可以使用名稱中帶有“ lookup”的功能。在本文中，我將解釋VLOOKUP和INDEX-MATCH。在此過程中，我將解釋這兩種方法的優缺點。當然，還有兩個附加的查找功能：HLOOKUP的工作方式與VLOOKUP相同，但它是水平而不是垂直。因此，我所說的關于VLOOKUP的所有內容也適用于HLOOKUP。LOOKUP旨在與Lotus 1-2-3兼容。和1-2-3的@LOOKUP函數旨在與VisiCalc兼容。因此，LOOKUP的功能不如VLOOKUP。但是，它確實比VLOOKUP具有一個優勢，我將在本系列的后續文章中對此進行討論。VLOOKUP函數具有以下參數：= VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）range_lookup參數是可選的，通常會被忽略，但非常有用。這是因為range_lookup確定VLOOKUP返回近似匹配還是精確匹配。盡管Excel實際上使用了另一種技術，但是以下是考慮近似匹配如何工作的簡單方法：Excel沿第一列向下移動，直到找到大于查找值的值。然后，它備份一行以返回其結果。如果查找值大于列中的最后一個數字，則它將提供最后一個結果。什么時候應該使用近似匹配？只有當您確實需要近似匹配時，才應該使用近似匹配。如果需要精確匹配，并且想要精確結果，則切勿使用近似匹配。默認情況下，VLOOKUP返回一個近似匹配。這是不幸的，有兩個原因。首先，以我的經驗，大多數Excel用戶在大多數情況下都需要完全匹配。其次，近似匹配是有風險的，因為它們可能返回錯誤的結果。該圖顯示了需要近似匹配的典型情況。該圖顯示了數量折扣的時間表。如果購買數量在1-4個之間，則不提供折扣。購買5到24個單位可獲得5％的折扣，依此類推。購買200個或更多單位可獲得20％的折扣。近似匹配功能使我們可以查找任何大小訂單的折扣。例如，單元格E4顯示，購買7個單位的折扣為5％。這是計算公式：
E4：= VLOOKUP（E $ 3，$ A $ 3：$ B $ 7,2，TRUE）（最后一個參數是可選的range_lookup值。由于該參數的默認值為TRUE，因此可以省略。）上圖中的單元格E7中的公式相似。它顯示了VLOOKUP對于大值可以按預期工作。也就是說，任何超過最大數量的購買都將獲得最大數量的折扣。請注意，圖中的單位是按升序排序的。這是一個絕對的要求。如果您的數據未排序，則可能得到錯誤的結果。如何從Excel查找功能獲取錯誤結果使用近似匹配時，必須對數據進行排序。對于所有返回近似匹配項的Excel查找函數，都是如此。達到此要求的原因是，Excel不僅會沿一列或跨行進行近似匹配。而是，Excel使用二進制搜索技術。即，Excel檢查列表的中間。如果該值小于查找值，則跳至下一部分的中間；否則，將跳轉到下一部分。如果該值較小，則跳到上一節的中間。它將距離減半，直到找到正確的結果。此技術比向下搜索一行或一列要快得多，但是如果不對數據進行排序，它可能會產生意外結果。通常，如果您對未排序的數據進行近似匹配，則Excel查找功能將返回錯誤值。這是最好的結果，因為它可以提醒您問題所在。近似匹配，未排序的數據但是不幸的是，當Excel對未排序的數據執行近似匹配時，它會產生錯誤的結果。例如，此圖說明了兩種錯誤的結果。
SKU＃1示例搜索表中未包含的值。示例中的兩個公式是：F3： = VLOOKUP（F $ 2，$ A $ 4：$ C $ 8,2，TRUE）
F4： = VLOOKUP（F $ 2，$ A $ 4：$ C $ 8,3，TRUE）VLOOKUP函數具有以下參數：= VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）由于在單元格F3和F4中range_lookup值為TRUE，因此這些公式正在尋找近似匹配項。從圖中可以看出，這兩個公式將返回SKU代碼A101的結果。這是一個重要的問題，因為Excel返回錯誤的結果，這很危險。SKU＃2示例使用類似的公式來搜索表中IS的值。但是，由于未對SKU列進行排序，因此它還會返回SKU代碼A101的值，而不是正確的結果。因此，兩個主要結論是顯而易見的：1.僅在有充分理由時才使用近似匹配。2.確定，當您使用近似匹配時，您的數據將被排序。如何通過Excel的VLOOKUP和HLOOKUP函數使用完全匹配VLOOKUP和HLOOKUP的默認行為是返回近似匹配。要了解為什么Microsoft可能會做出此選擇，請考慮至少在20年前編寫了Excel的四個查找功能（LOOKUP，VLOOKUP，HLOOKUP和MATCH）。那時，計算機比現在慢得多。那時，使用完全匹配搜索會導致計算明顯變慢。因此，Excel團隊將最快的方法（近似匹配方法）設置為默認搜索方法。但是今天，對于大多數目的而言，精確匹配和近似匹配之間的計算時間沒有明顯差異。因此，今天，由于至少兩個原因，Excel的默認搜索方法很不幸。首先，Excel用戶通常比完全匹配更需要完全匹配。其次，近似匹配會產生錯誤的結果。讓我們看看精確匹配如何與典型數據一起工作……F2和F3單元格中的公式返回正確的結果。而且由于單元格F6和F7中的公式會查找不存在的數據，因此它們也會返回正確的結果。兩個示例顯示正確使用VLOOKUP公式。AC列顯示了一個簡單的SKU（庫存單位）數據庫及其說明和價格。F列顯示了兩組示例。
請注意，A列中的SKU數據未排序。使用完全匹配時，排序順序無關緊要。SKU＃1示例使用以下公式搜索有關SKU代碼B19的信息：
F2： = VLOOKUP（F $ 1，$ A $ 3：$ C $ 7,2，FALSE）
F3： = VLOOKUP（F $ 1，$ A $ 3：$ C $ 7,3，FALSE）請記住，VLOOKUP函數具有以下參數：= VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）由于這些公式中的range_lookup值為FALSE，因此它們正在尋找精確匹配。并且由于表中存在SKU代碼B19，因此公式可以找到并返回正確的結果。這真是個好消息……我們得到了正確的結果！SKU＃2示例提供了更好的消息。以下是其公式：F6： = VLOOKUP（F $ 5，$ A $ 3：$ C $ 7,2，FALSE）
F7： = VLOOKUP（F $ 5，$ A $ 3：$ C $ 7,3，FALSE）在這里，當我們搜索不在列表中的數據時會得到錯誤值。那是更好的消息，因為我們沒有收到虛假數據。也就是說，當我們僅使用精確匹配時，如果我們搜索不存在的數據，則可以依靠獲得錯誤值。VLOOKUP和HLOOKUP的局限性
VLOOKUP公式使用以下語法：??= VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）讓我們使用此函數從這個小數據庫中查找一些值。要在SKU列中查找“ G23”并返回其描述，我們將使用：= VLOOKUP（“ G23”，$ A $ 3：$ C $ 7,2，FALSE）要在SKU中查找“ G23”并返回其價格，我們將使用：= VLOOKUP（“ G23”，$ A $ 3：$ C $ 7,3，FALSE）要在“項目”列中查找“外套”并返回其價格，我們將使用：= VLOOKUP（“大衣”，$ B $ 3：$ C $ 7,2，FALSE）沒關系。但是，我們如何使用VLOOKUP在“項目”列中查找“鞋子”并返回其SKU？我們不能。原因很簡單。VLOOKUP只能在table_array的第一列中查找值，而我們只能從table數組中返回值。因此，如果我們想在一個列中查找一個值，然后將值返回到該列的左側，那么我們就不走運了。（不，如果我們嘗試對col_index_num使用負值，則將無法使用。）要返回我們需要的值，您會認為我們可以使用具有以下格式的LOOKUP函數：= LOOKUP（lookup_value，lookup_vector，result_vector）問題在于，LOOKUP的lookup_vector列必須按升序排序。因此，如果我們的數據庫按Item列排序，我們可以返回其SKU，這是VLOOKUP無法做到的。但是因為數據沒有排序，所以我們很不走運……至少對于LOOKUP，VLOOKUP和HLOOKUP。不幸的是，這不是這三個功能的唯一限制。局限性是為什么自1990年以來我就不再使用這三個函數。相反，我僅使用INDEX-MATCH。Excel的最佳查找方法：INDEX-MATCH在Excel中查找數據的最強大，最靈活的方法是INDEX – MATCH方法。它依賴于兩個Excel函數：= INDEX（參考，row_num，column_num）參考 —單元格范圍row_num —參考中要從中返回數據的行。column_num-參考中要從中返回數據的列。如果引用是一行或一列，則 INDEX函數可以使用以下語法：= INDEX（reference，cell_num）= MATCH（lookup_value，lookup_array，match_type）lookup_value-在lookup_array中匹配的值。lookup_array-包含數據的單元格范圍。match_type-指定 Excel如何將lookup_value與lookup_array中的值匹配。對于完全匹配，請始終將此參數使用0。下圖顯示了正在使用的INDEX-MATCH方法…B11： = MATCH（$ A11，$ A $ 3：$ A $ 7,0）該公式告訴我們，在SKU列的第三行中找到了文本“ G23”。復制到B12的公式表示在第二行中找到“ A101”。請注意，由于我們需要精確匹配，因此單元格B11中的最后一個參數的值為零。
C11： = INDEX（$ B $ 3：$ B $ 7，$ B11）單元格C11的此公式返回由單元格B11中的值指定的單元格編號的項目。D11： = INDEX（$ C $ 3：$ C $ 7，$ B11）并且該單元格D11的公式返回單元格B11中指定的單元格編號的價格。當然，我們可以將INDEX和MATCH合并為一個公式，如單元格E11中所示：E11： = INDEX（$ C $ 3：$ C $ 7，MATCH（$ A11，$ A $ 3：$ A $ 7,0））此組合的工作方式與單元格F11中的VLOOKUP函數類似：F11： = VLOOKUP（$ A11，$ A $ 3：$ C $ 7,3，FALSE）到目前為止，INDEX-MATCH和VLOOKUP具有相同的功能。但是現在考慮單元格B16和C16中的公式：
B16： = MATCH（$ A16，$ B $ 3：$ B $ 7,0）
C16： = INDEX（$ A $ 3：$ A $ 7，$ B16）在這兩個公式中，我們在“項目”列中查找“領帶”，并從SKU列中返回其SKU。如上所述，這是VLOOKUP無法產生的結果。更常見的是，我們將這些公式組合為一個：E16： = INDEX（$ C $ 3：$ C $ 7，MATCH（$ A16，$ B $ 3：$ B $ 7,0））使用此方法時，您會發現INDEX-MATCH方法還有許多其他優點。如果您沒有使用INDEX-MATCH進行查找，則可能會丟失很多功能。試試看。
