教程展示了一些不同的公式，以在Excel中執(zhí)行二維查找。只需瀏覽替代方案，然後選擇您的喜歡:)

在搜索Excel電子表格中的某些內(nèi)容時，大多數(shù)時候您會在列中或水平瀏覽。但是有時您需要瀏覽行和列。換句話說，您的目標(biāo)是在某個行和列的交集處找到一個值。這稱為Matrix查找（又稱2維查找），該教程顯示瞭如何以4種不同的方式進(jìn)行操作。

Excel索引匹配公式

在Excel中進(jìn)行雙向查找的最流行方式是使用索引匹配。這是經(jīng)典索引匹配公式的一種變體，您可以在其中添加一個匹配功能，以便獲得行號和列號：

索引（ data_array ，match（ vlookup_value ， lookup_column_range ，0），匹配（ hlookup value ， lookup_row_range ，0））

例如，讓我們製作一個公式，以從下表中從給定的一年中拉出某種動物的種群。對於初學(xué)者，我們定義了所有論點(diǎn)：

• data_array -b2：e4（數(shù)據(jù)單元，不包括行和列標(biāo)題）
• vlookup_value -H1（目標(biāo)動物）
• Lookup_column_range -A2：A4（行標(biāo)頭：動物名稱） - A3：A4
• Hlookup_value -H2（目標(biāo)年）
• Lookup_row_range -B1：E1（列標(biāo)題：年）

將所有參數(shù)放在一起，您將獲得此公式進(jìn)行雙向查找：

=INDEX(B2:E4, MATCH(H1, A2:A4, 0), MATCH(H2, B1:E1, 0))

如果您需要使用兩個以上標(biāo)準(zhǔn)進(jìn)行雙向查找，請查看本文：索引與行和列中的多個標(biāo)準(zhǔn)匹配。

該公式如何工作

乍一看可能看起來有些複雜，但公式的邏輯確實(shí)很簡單且易於理解。索引函數(shù)根據(jù)行和列號從數(shù)據(jù)陣列中檢索一個值，並且兩個匹配函數(shù)提供了這些數(shù)字：

INDEX(B2:E4, row_num, column_num)

在這裡，我們利用匹配的能力（lookup_value，lookup_array，[match_type]）返回lookup_value在lookup_array中的相對位置。

因此，要獲取行號，我們在行標(biāo)頭（A2：A4）上搜索感興趣的動物（H1）：

MATCH(H1, A2:A4, 0)

要獲取列號，我們在整個列標(biāo)題（B1：E1）上搜索目標(biāo)年度（H2）：

MATCH(H2, B1:E1, 0)

在這兩種情況下，我們都通過將第三個參數(shù)設(shè)置為0來查找確切的匹配。

在此示例中，第一匹匹配返回2，因?yàn)槲覀兊腣lookup值（北極熊）在A3中找到，該值是A2：A4中的2^個單元格。第二個匹配返回3，因?yàn)樵贒1中發(fā)現(xiàn)了Hlookup值（2000），即B1：E1中的3 ^rd單元格。

鑑於上述，該公式減少為：

INDEX(B2:E4, 2, 3)

並在數(shù)據(jù)陣列b2：e4中的^第2行和3^列列的相交處返回一個值，該值是單元格D3中的一個值。

vlookup和2向查找的匹配公式

在Excel中進(jìn)行二維查找的另一種方法是使用vlookup和匹配函數(shù)的組合：

vlookup（ vlookup_value ， table_array ，match（ hlookup_value ， lookup_row_range ，0），false），false）

對於我們的示例表，公式採用以下形狀：

=VLOOKUP(H1, A2:E4, MATCH(H2, A1:E1, 0), FALSE)

在哪裡：

• table_array -a2：e4（包括行標(biāo)頭的數(shù)據(jù)單元格）
• vlookup_value -H1（目標(biāo)動物）
• Hlookup_value -H2（目標(biāo)年）
• Lookup_row_range -A1：E1（列標(biāo)題：年）

該公式如何工作

公式的核心是為精確匹配配置的vlookup函數(shù)（最後一個參數(shù)設(shè)置為false），該函數(shù)在表陣列（A2：e4）的第一列中搜索查找值（H1），並從同一行中的另一列返回一個值。要確定要從哪個列返回值的列，您使用的匹配函數(shù)也配置為確切匹配（最後一個參數(shù)設(shè)置為0）：

MATCH(H2, A1:E1, 0)

匹配搜索在列標(biāo)頭（A1：E1）的H2中的值，並返回發(fā)現(xiàn)的單元格的相對位置。在我們的情況下，目標(biāo)年（2010年）在E1中發(fā)現(xiàn)，該陣列位於查找陣列中的^第5個。因此，數(shù)字5轉(zhuǎn)到vlookup的col_index_num參數(shù)：

VLOOKUP(H1, A2:E4, 5, FALSE)

Vlookup從那裡拿走它，找到A2中其查找值的確切匹配，並從同一行中的^第5列返回一個值，即單元E2。

重要說明！為了使公式正確工作，vookup的table_array （A2：e4）和Match的Lookup_array （A1：E1）必須具有相同數(shù)量的列，否則匹配的數(shù)字通過col_index_num傳遞給col_index_num將不正確（與table_array中的列的位置不符）。

xlookup函數(shù)以行和列查看

最近，Microsoft在Excel中引入了另一個功能，該功能旨在替換所有現(xiàn)有的查找功能，例如VlookUp，Hlookup和Index Match。除其他外，Xlookup可以查看特定行和列的交集：

Xlookup（ vlookup_value ， vlookup_column_range ，xlookup（ hlookup_value ， hlookup_row_range ， data_array ））

對於我們的示例數(shù)據(jù)集，公式如下：

=XLOOKUP(H1, A2:A4, XLOOKUP(H2, B1:E1, B2:E4))

筆記。 Xlookup功能僅在Microsoft 365，Excel 2021和excel的Excel中可用。

該公式如何工作

該公式使用Xlookup返回整個行或列的能力。內(nèi)部功能在標(biāo)題行中搜索目標(biāo)年份，並返回該年的所有值（在本示例中為1980年）。這些值轉(zhuǎn)到外部Xlookup的return_array參數(shù)：

XLOOKUP(H1, A2:A4, {22000;25000;700}))

外部Xlookup函數(shù)在列標(biāo)頭上搜索目標(biāo)動物，並從return_array返回相同位置的值。

雙向查找的Sumproduct公式

Sumproduct功能就像Excel中的瑞士刀一樣 - 它可以做很多超出其指定目的的事情，尤其是在評估多個標(biāo)準(zhǔn)時。

要查找以行和列中的兩個標(biāo)準(zhǔn)，請使用此通用公式：

sumproduct（ vlookup_column_range = vlookup_value ） *（ hlookup_row_range = hlookup_value ）， data_array ）

要在我們的數(shù)據(jù)集中執(zhí)行2條查找，該公式如下：

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2), B2:E4)

以下語法也將起作用：

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2) * B2:E4)

該公式如何工作

在公式的核心中，我們將兩個查找值與行和柱標(biāo)題（H1中的目標(biāo)動物與A2：A4中的所有動物名稱和H2中的所有動物名稱與B1：E1中的所有年份）進(jìn)行了比較）：

(A2:A4=H1) * (B1:E1=H2)

這將產(chǎn)生2個真實(shí)和錯誤值的數(shù)組，其中True's代表匹配：

{FALSE;FALSE;TRUE} * {FALSE,TRUE,FALSE,FALSE}

乘法操作將真實(shí)值和錯誤值脅迫到1和0，並產(chǎn)生4列和3行的二維數(shù)組（行被分號分離，每列數(shù)據(jù)列以逗號分隔）：

{0,0,0,0;0,0,0,0;0,1,0,0}

SumProduct的功能將上述數(shù)組的元素乘以同一位置的B2：E4的項(xiàng)目：

{0,0,0,0;0,0,0,0;0,1,0,0} * {22000,13800,8500,3500;25000,23000,22000,20000;700,2000,2300,2500}

而且由於乘以零的乘以零，因此僅在第一個數(shù)組中對應(yīng)於1的項(xiàng)目倖存下來：

SUMPRODUCT({0,0,0,0;0,0,0,0;0,2000,0,0})

最後，sumproduct添加了結(jié)果數(shù)組的元素，並返回了2000年的值。

筆記。如果您的表具有具有相同名稱的一行或/和列標(biāo)題，則最終數(shù)組將包含以上的一個以上的數(shù)字，並且所有這些數(shù)字都將被添加。結(jié)果，您將獲得符合這兩個條件的值的總和。這就是使Sumproduct公式與索引匹配和Vlookup不同的原因，後者返回第一個發(fā)現(xiàn)的匹配。

帶有命名範(fàn)圍的矩陣查找（顯式交叉）

在Excel中進(jìn)行矩陣查找的一種更簡單的方法是使用命名範(fàn)圍。以下是：

第1部分：名稱列和行

命名每一行的最快方法和表格中的每一列是：

1. 選擇整個表（在我們的情況下，A1：E4）。
2. 在“公式”選項(xiàng)卡上，在定義的名稱組中，單擊“從選擇”或按CTRL Shift F3快捷方式。
3. 在“從選擇”對話框的“創(chuàng)建名稱”中，選擇頂行和左列，然後單擊“確定”。

   

這將根據(jù)行和列標(biāo)題自動創(chuàng)建名稱。但是，有幾個警告：

• 如果您的列和/或行標(biāo)頭是數(shù)字或包含Excel名稱中不允許的特定字符，則不會創(chuàng)建此類列和行的名稱。要查看創(chuàng)建名稱的列表，請打開名稱管理器（ CTRL F3 ）。如果缺少某些名稱，請按照如何在Excel中命名範(fàn)圍中的解釋。
• 如果您的某些行或列標(biāo)題包含空格，則將其空間替換為下劃線，例如Polar_bear 。

對於我們的示例表，Excel僅自動創(chuàng)建了行名。由於列標(biāo)題是數(shù)字，因此必須手動創(chuàng)建列名。為了克服這一點(diǎn)，您可以簡單地用_1990來將數(shù)字置於下劃線。

結(jié)果，我們有以下命名範(fàn)圍：

第2部分：製作矩陣查找公式

要在給定的行和列的交點(diǎn)上拉一個值，只需在空單元格中鍵入以下通用公式之一：

= row_name column_name

反之亦然：

= column_name row_name

例如，為了在1990年獲得藍(lán)鯨的種群，該公式很簡單：

=Blue_whale _1990

如果某人需要更詳細(xì)的說明，以下步驟將使您完成該過程：

1. 在您希望結(jié)果出現(xiàn)的單元格中，鍵入相等符號（=）。
2. 開始鍵入目標(biāo)行的名稱，例如blue_whale 。輸入幾個字符後，Excel將顯示所有與輸入匹配的現(xiàn)有名稱。雙擊所需的名稱以將其輸入您的公式：

   

3. 在行名之後，鍵入A空間，在這種情況下，該空間可用作交集操作員。
4. 輸入目標(biāo)列名稱（在我們的情況下為_1990 ）。

   

5. 一旦輸入了行和列名，Excel將突出顯示表中的相應(yīng)行和列，然後按Enter完成公式：

   

您的矩陣查找已完成，以下屏幕截圖顯示了結(jié)果：

這就是如何在Excel中以行和列查找的方法。我感謝您閱讀，並希望下週在我們的博客上見到您！

可用下載

二維查找樣本工作簿

以上是二維查找中Excel中的索引匹配的詳細(xì)內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！