本教程解釋了如何使Excel的VLOOKUP函數(shù)區(qū)分大小寫，展示了一些其他能識別文本大小寫的公式，并指出了每個函數(shù)的優(yōu)點和局限性。

我想每個使用Excel的用戶都知道Excel中執(zhí)行垂直查找的函數(shù)是什么。沒錯，就是VLOOKUP。然而，很少有人知道Excel的VLOOKUP是大小寫不敏感的，意味著它將小寫和大寫字母視為相同的字符。

下面是一個快速示例，展示了VLOOKUP無法區(qū)分文本大小寫的能力。假設(shè)你在A2單元格中輸入"bill"，在A4中輸入"Bill"。以下公式將捕獲"bill"，因為它在查找數(shù)組中排在首位，并返回B2中的匹配值。

=VLOOKUP("Bill", A2:B4, 2, FALSE)

在本文的后面部分，我將向你展示如何使VLOOKUP區(qū)分大小寫。我們還將探索其他幾個在Excel中能執(zhí)行大小寫敏感匹配的函數(shù)。

區(qū)分大小寫的VLOOKUP公式 ------------------------------

如上所述，常規(guī)的VLOOKUP公式不識別字母大小寫。然而，有一種方法可以使Excel的VLOOKUP區(qū)分大小寫，如下例所示。

假設(shè)你在A列中輸入商品ID，并希望從B列和C列中拉取商品的價格和評論。問題是ID包括大小寫字符。例如，A4中的值（001Tvci3u）和A5中的值（001Tvci3U）僅在最后一個字符不同，分別是"u"和"U"。

在查找"001Tvci3U"時，標準的VLOOKUP公式會輸出與"001Tvci3u"相關(guān)的$90，因為它在查找數(shù)組中排在"001Tvci3U"之前。但這并不是你想要的結(jié)果，對嗎？

=VLOOKUP(F2, A2:C7, 2, FALSE)

為了在Excel中執(zhí)行區(qū)分大小寫的查找，我們結(jié)合使用VLOOKUP、CHOOSE和EXACT函數(shù)：

VLOOKUP(TRUE, CHOOSE({1,2}, EXACT(查找值, 查找數(shù)組), 返回數(shù)組), 2, 0) 這個通用公式在所有情況下都能完美工作。你甚至可以從右到左進行查找，這是常規(guī)VLOOKUP公式無法做到的。感謝Pouriya提出了這個簡單而優(yōu)雅的解決方案！

在我們的案例中，實際公式如下。

為了在F3中拉取價格：

=VLOOKUP(TRUE, CHOOSE({1,2}, EXACT(F2, A2:A7), B2:B7), 2, FALSE)

為了在F4中獲取評論：

=VLOOKUP(TRUE, CHOOSE({1,2}, EXACT(F2, A2:A7), C2:C7), 2, FALSE)

注意。在除Excel 365之外的所有Excel版本中，這只能作為數(shù)組公式使用，因此請記得按Ctrl Shift Enter鍵正確完成它。在Excel 365中，由于支持動態(tài)數(shù)組，它也可以作為常規(guī)公式使用。

這個公式是如何工作的：

核心部分是嵌套了EXACT的CHOOSE公式：

CHOOSE({1,2}, EXACT(F2, A2:A7), C2:C7)

在這里，EXACT函數(shù)將F2中的值與A2:A7中的每個值進行比較，并在它們完全相同（包括字母大小寫）時返回TRUE，否則返回FALSE：

{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

對于CHOOSE的index_num參數(shù)，我們使用數(shù)組常量{1,2}。結(jié)果，函數(shù)將上述數(shù)組中的邏輯值和C2:C7中的值組合成一個二維數(shù)組，如下所示：

{FALSE,155;FALSE,186;FALSE,90;TRUE,54;FALSE,159;FALSE,28}

VLOOKUP函數(shù)從那里接手，在二維數(shù)組的第1列（由邏輯值表示）中查找查找值（即TRUE），并從第2列返回匹配值，即我們正在查找的價格：

VLOOKUP(TRUE, {FALSE,155;FALSE,186;FALSE,90;TRUE,54;FALSE,159;FALSE,28}, 2, 0)

區(qū)分大小寫的XLOOKUP公式

Microsoft 365訂閱者可以使用更簡單的公式在Excel中執(zhí)行區(qū)分大小寫的查找。正如你所猜到的，我在談?wù)揤LOOKUP的更強大繼承者——XLOOKUP函數(shù)。

因為XLOOKUP分別操作查找和返回數(shù)組，我們不需要像前一個例子中的二維數(shù)組技巧。簡單地使用EXACT作為查找數(shù)組參數(shù)：

XLOOKUP(TRUE, EXACT(查找值, 查找數(shù)組), 返回數(shù)組, "未找到") 最后一個參數(shù)（"未找到"）是可選的。它只是定義了在未找到匹配項時返回的值。如果你省略它，則在公式找不到匹配項時會返回標準的#N/A錯誤。

對于我們的樣本表，這些是我們使用的區(qū)分大小寫的XLOOKUP公式。

為了在F3中獲取價格：

=XLOOKUP(TRUE, EXACT(F2, A2:A7), B2:B7, "未找到")

為了在F4中提取評論：

=XLOOKUP(TRUE, EXACT(F2, A2:A7), C2:C7, "未找到")

這個公式是如何工作的：

像前一個例子一樣，EXACT返回TRUE和FALSE值的數(shù)組，其中TRUE表示區(qū)分大小寫的匹配。XLOOKUP在上述數(shù)組中查找TRUE值，并從返回數(shù)組中返回匹配項。請注意，如果查找列中有兩個或更多完全相同的值（包括字母大小寫），公式將返回第一個找到的匹配項。

XLOOKUP的限制：僅在Excel 365和Excel 2021中可用。

SUMPRODUCT - 用于返回匹配數(shù)字的區(qū)分大小寫查找

正如標題所示，SUMPRODUCT是另一個可以執(zhí)行區(qū)分大小寫查找的Excel函數(shù)，但它只能返回數(shù)值。如果這不符合你的需求，請?zhí)絀NDEX MATCH示例，它為所有數(shù)據(jù)類型提供了解決方案。

正如你可能知道的，Excel的SUMPRODUCT函數(shù)會將指定數(shù)組中的組件相乘并返回乘積的總和。由于我們希望進行區(qū)分大小寫的查找，我們使用EXACT函數(shù)來獲取第一個數(shù)組：

=SUMPRODUCT((EXACT(A2:A7,F2) * (B2:B7)))

遺憾的是，SUMPRODUCT函數(shù)無法返回文本匹配，因為文本值無法相乘。在這種情況下，你會在下面的屏幕截圖中的F4單元格中得到一個#VALUE!錯誤：

這個公式是如何工作的：

像VLOOKUP示例一樣，EXACT函數(shù)將F2中的值與A2:A7中的所有值進行比較，并在區(qū)分大小寫的匹配時返回TRUE，否則返回FALSE：

SUMPRODUCT(({FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}*{155;186;90;54;159;28}))

在大多數(shù)公式中，Excel將TRUE評估為1，將FALSE評估為0。因此，當SUMPRODUCT將兩個數(shù)組中相同位置的元素相乘時，所有非匹配項（FALSE）都變?yōu)榱悖?/p>

SUMPRODUCT({0;0;0;54;0;0})

結(jié)果，公式返回與A列中精確區(qū)分大小寫的匹配項對應(yīng)的B列中的數(shù)字。

SUMPRODUCT的限制：只能返回數(shù)值。

INDEX MATCH - 適用于所有數(shù)據(jù)類型的區(qū)分大小寫查找

最后，我們即將獲得一個無限制的區(qū)分大小寫查找公式，它適用于所有Excel版本和所有數(shù)據(jù)集。

這個例子之所以放在最后，不僅因為最好的留到最后，也是因為你在前面的例子中獲得的知識可能會幫助你更好地理解區(qū)分大小寫的MATCH INDEX公式。

INDEX和MATCH函數(shù)的組合在Excel中經(jīng)常被用作VLOOKUP的更靈活和多功能的替代方案。以下文章很好地解釋了這兩個函數(shù)如何一起工作 - 使用INDEX MATCH代替VLOOKUP。

在這里，我只提醒你一些關(guān)鍵點：

• MATCH函數(shù)在指定的查找數(shù)組中搜索查找值，并返回其相對位置。
• 查找值的相對位置直接傳遞給INDEX函數(shù)的row_num參數(shù)，指導它從該行返回一個值。

為了使公式識別文本大小寫，你只需在經(jīng)典的INDEX MATCH組合中添加一個函數(shù)。顯然，你再次需要EXACT函數(shù)：

INDEX(返回數(shù)組, MATCH(TRUE, EXACT(查找值, 查找數(shù)組), 0)) F3中的實際公式是：

=INDEX(B2:B7, MATCH(TRUE, EXACT(A2:A7, F2), 0))

在F4中，我們使用以下公式：

=INDEX(C2:C7, MATCH(TRUE, EXACT(A2:A7, F2), 0))

請記住在除Excel 365之外的所有版本中，它只能作為數(shù)組公式使用，因此請確保通過同時按下Ctrl Shift Enter鍵輸入它。如果正確完成，公式將被括在大括號中，如下面的屏幕截圖所示：

這個公式是如何工作的：

像所有前面的例子一樣，EXACT為A2:A7中每個與F2中值完全匹配的值返回TRUE。由于我們使用TRUE作為MATCH的查找值，它返回精確區(qū)分大小寫的匹配項的相對位置，這正是INDEX需要從B2:B7中返回匹配項所需的。

高級區(qū)分大小寫查找公式

上面提到的INDEX MATCH公式看起來很完美，對嗎？但事實上，它并不完美。讓我告訴你為什么。

假設(shè)返回列中對應(yīng)查找值的單元格是空白的。公式應(yīng)該返回什么？什么也不返回?，F(xiàn)在，讓我們看看它實際返回了什么：

=INDEX(C2:C7, MATCH(TRUE, EXACT(A2:A7, F2), 0))

哦，公式返回了零！也許在僅處理文本值時這并不重要。然而，如果你的工作表包含數(shù)字，并且其中一些是真正的零，這就是一個問題。

事實上，前面討論的所有其他查找公式也以同樣的方式表現(xiàn)。但現(xiàn)在你想要一個完美的公式，不是嗎？

為了使區(qū)分大小寫的INDEX MATCH公式絕對完美，你可以用IF函數(shù)包裝它，該函數(shù)檢查返回單元格是否為空，并在這種情況下返回空值：

=IF(INDIRECT("C"&(1 MATCH(TRUE,EXACT(A2:A7, F2), 0)))"", INDEX(C2:C7, MATCH(TRUE, EXACT(A2:A7, F2), 0)), "")

在上述公式中：

• "C"是返回列。
• "1"是將MATCH函數(shù)返回的單元格的相對位置轉(zhuǎn)換為實際單元格地址的數(shù)字。

例如，我們的MATCH函數(shù)中的查找數(shù)組是A2:A7，這意味著A2單元格的相對位置是"1"，因為這是數(shù)組中的第一個單元格。但實際上，查找數(shù)組從第2行開始。為了補償差異，我們加上1，因此INDIRECT函數(shù)將返回來自正確單元格的值。

下面的屏幕截圖展示了改進后的區(qū)分大小寫的INDEX MATCH公式的實際效果。

如果返回單元格為空，公式輸出空字符串：

如果返回單元格包含零，公式返回0：

如果你寧愿在返回單元格為空時顯示一些消息，請在IF的最后一個參數(shù)中用一些文本替換空字符串（""）：

=IF(INDIRECT("C"&(1 MATCH(TRUE, EXACT(A2:A7, F2), 0)))"", INDEX(C2:C7, MATCH(TRUE, EXACT(A2:A7, F2), 0)), "沒有可返回的內(nèi)容，很抱歉。")

輕松進行區(qū)分大小寫的VLOOKUP

我們的Excel終極套件的用戶有一個特殊工具，使在大型和復雜表格中查找變得更容易和無壓力。最好的地方是合并兩個表格工具有一個區(qū)分大小寫的選項，以下示例展示了它的實際效果。

假設(shè)你想根據(jù)唯一的商品ID從查找表中拉取Qty.到主表中：

你要做的就是運行合并表向?qū)Р?zhí)行以下步驟：

1. 選擇要拉取新數(shù)據(jù)的主表。
2. 選擇查找新數(shù)據(jù)的查找表。
3. 選擇一個或多個關(guān)鍵列（在我們的案例中是商品ID）。并確保勾選區(qū)分大小寫匹配框。
4. 向?qū)⒁龑阃瓿墒Ｏ碌娜齻€步驟，在那里你指定要更新哪些列，要添加哪些列，并根據(jù)需要選擇一些其他選項。

片刻之后，你將得到想要的結(jié)果 :)

這就是如何在Excel中進行考慮文本大小寫的查找。感謝你閱讀，希望下周在我們的博客上見到你！

練習工作簿下載

區(qū)分大小寫的VLOOKUP示例（.xlsx文件）

以上是如何在excel中進行案例敏感的vlookup - 公式示例的詳細內(nèi)容。更多信息請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！