之前給大家推送了一篇用PQ完成跨表數(shù)據(jù)核對的教程，但由于版本限制，好多伙伴都無法使用，今天給大家介紹3個公式，同樣可以完成數(shù)據(jù)核對。

最近在微信學(xué)習(xí)交流群中收到某位學(xué)員的問題咨詢，問題是如何根據(jù)單據(jù)編號和物料長代碼返回對應(yīng)的含稅數(shù)額。如下表：

其實(shí)這位學(xué)員的問題就是如何實(shí)現(xiàn)多條件查詢。

下面通過一個實(shí)例跟大家分享一下常用的幾種多條件查詢方法。

下表是某電商公司的客戶投訴表，現(xiàn)在需要通過A表中的客戶姓名與地區(qū)兩個條件來查詢B表中的產(chǎn)品型號，返回到A表的E列中。

1.lookup函數(shù)

函數(shù)公式：=LOOKUP(1,0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19),$K$3:$K$19)

公式解析：首先通過A3單元格與B表I列數(shù)據(jù)做對比，同時用B3單元格與B表J列信息做對比。

在excel中如果兩個單元格對比，相等則返回TRUE，在四則運(yùn)算中用1表示。如果不相等則返回FALSE，使用0表示。

那么(A3=$I$3:$I$19)*(B3=$J$3:$J$19)這部分運(yùn)算的結(jié)果就只有0或者1兩種情況，因?yàn)橹挥?*1、1*1、1*0這三種情況。

用0來除以0和1，由于分母不能為0，所以0/0返回的是錯誤，0/1返回的結(jié)果為0。Lookup函數(shù)在查找的時候是忽略錯誤的，所以只有數(shù)據(jù)運(yùn)算結(jié)果為1的公式滿足條件。

那么我們就很好理解0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19)的目的就是將正確結(jié)果用0表示，其他的變成錯誤值，利用函數(shù)查找忽略錯誤這個特點(diǎn)完成查找。

總結(jié)：本函數(shù)由于使用了二分法原理查找，所以如果數(shù)據(jù)量較大時運(yùn)算會很慢。

2.VLOOKUP函數(shù)

使用G2單元格在A列中查找，如果查找到對應(yīng)單元格則返回A列向右第二列的數(shù)據(jù)。簡而言之：=VLOOKUP(查找什么，在哪查找，從條件所在列算起找到后返回對應(yīng)的第幾列數(shù)據(jù)，精確或模糊查找)。

那vlookup如何才能完成多條件查詢呢？。

還以客戶投訴表為例，按照姓名&地區(qū)來匹配產(chǎn)品型號返回到E里中。

其實(shí)我們是可以將A、B兩表中插入輔助列，將姓名和地區(qū)都合并到一個單元格中然后使用vlookup來完成。

但是插入2個輔助列后整個表列數(shù)發(fā)生變動，在工作中往往單元格中有很多公式，如果列數(shù)發(fā)生變化將直接導(dǎo)致表格中函數(shù)公式運(yùn)算結(jié)果錯誤。所以添加輔助列的方式雖然簡單，但不是最好的方式。

那么不用輔助列如何才能完成多條件查詢呢？

首先我們查找值合并很簡單，輸入函數(shù)vlookup時第一個參數(shù)可以寫成A3&B3，即可將A3、B3兩個單元格內(nèi)容合并，作為查找值。

現(xiàn)在問題查找區(qū)域也需要做合并。

如果把兩列內(nèi)容合并在一起，可輸入公式=H2:H19&I2:I19，按ctrl+shift+回車生成結(jié)果，然后下拉公式，這樣兩個條件就變成了一個。

接下來通過IF函數(shù)提取對應(yīng)的J列數(shù)據(jù)，可輸入公式

=IF({0,1},H2:H19&I2:I19,J2:J19)，按ctrl+shift+回車生成結(jié)果，然后下拉公式，{0,1}表示邏輯值{FALSE,TRUE}。

下面我們詳細(xì)來解析一下:

首先在excel中0表示錯誤，1以及其他所有數(shù)值表示正確。如下表示例：

通過上面的例子我看到如果IF判斷0則返回錯誤，判斷1則返回正確。

現(xiàn)在我們可以將公式拆分為以下兩種情況：

IF（0, H2:H19&I2:I19,J2:J19），0表示FALSE，所以只能返回J列數(shù)據(jù)。

IF（1, H2:H19&I2:I19,J2:J19），1表示TRUE，所以只能返回H列和I列合并結(jié)果。

那么IF({0,1},H2:H19&I2:I19,J2:J19)怎么理解呢？

既然是數(shù)組公式，那么可以將它理解為同時返回兩組數(shù)據(jù)，0對應(yīng)的是J2:J19，1對應(yīng)的H2:H19&I2:I19，構(gòu)建了兩列數(shù)據(jù)。

最后我們使用vlookup函數(shù)完成嵌套，

=VLOOKUP(A3&B3,IF({1,0},H3:H20&I3:I20,J3:J20),2,0)，這里我們就可以理解為用A3&B3在H3:H20&I3:I20中查找對應(yīng)J3:J20中的數(shù)據(jù)。因?yàn)楣街?/p>

IF({1,0},H3:H20&I3:I20,J3:J20)返回的順序是先返回H3:H20&I3:I20再返回J3:J20。

注意：很多人不明白為什么嵌套的時候IF第一參數(shù)又變成了{(lán)1,0}，因?yàn)檫@里我們需要返回的是H和I合并結(jié)果作為查找區(qū)域。PS：所有數(shù)組公式完成輸入后要使用數(shù)組三鍵ctrl+shift+ener來返回運(yùn)算結(jié)果！

這樣我們不用輔助列也能通過vlookup函數(shù)完成多條件查詢。

3.OFFSET+MATCH函數(shù)

下面舉例跟大家分享一下通過offset函數(shù)完成多條件查詢。

函數(shù)公式：

{=OFFSET($J$2,MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0),)}

公式解析：

完成多條件查詢第一步先要確定A表中姓名&地區(qū)合并后對應(yīng)在B表中姓名&地區(qū)的順序。這里我們通過MATCH來完成，我們用個簡單的例子說明。

=MATCH(A2,E:E,0)表示使用A2單元格在E列中查找，0表示精確查找、1小于、-1大于，通常情況下都是精確查找。

MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0)表示將A3與B3合并作為查找內(nèi)容，H列和I列合并作為查找區(qū)域，0表示精確查找。

確定順序后我們通過OFFSET函數(shù)以順序數(shù)據(jù)作為偏移行數(shù)返回對應(yīng)數(shù)值。

OFFSET函數(shù)的功能是以指定的單元格引用為參照系，通過給定偏移量得到新的引用。

返回的引用可以為一個單元格或區(qū)域。并可以指定返回的行數(shù)或列數(shù)。Reference 作為偏移量參照系的引用區(qū)域。Reference 必須為對單元格或相連單元格區(qū)域的引用；否則，函數(shù) OFFSET 返回錯誤值#VALUE!。

=OFFSET(J2,1,0,1,1)表示以J2單元格作為參照物向下偏移1行，向右偏移0列，返回1行1列數(shù)據(jù)區(qū)域。

=OFFSET($J$2,MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0),)表示以$J$2為參照單元格，通過MATCH查找出來順序作為向下偏移的行數(shù)，偏移列數(shù)量省略表示不偏移，第三個、第四個參數(shù)省略表示只返回一個單元格區(qū)域。

下面我們來總結(jié)一下三種方式的利弊：LOOKUP函數(shù)使用過程中運(yùn)算較慢；VLOOKUP函數(shù)使用IF（{0,1}）數(shù)組公式，理解上存在一定難度；OFFSET+MATCH函數(shù)公式簡單，可以作為首選方案。

＊＊＊＊部落窩教育－excel數(shù)據(jù)核對公式應(yīng)用＊＊＊＊

原創(chuàng)：龔春光/部落窩教育（未經(jīng)同意，請勿轉(zhuǎn)載）