之前給大家推送了一篇用PQ完成跨表數(shù)據(jù)核對(duì)的教程，但由于版本限制，好多伙伴都無(wú)法使用，今天給大家介紹3個(gè)公式，同樣可以完成數(shù)據(jù)核對(duì)。
最近在微信學(xué)習(xí)交流群中收到某位學(xué)員的問(wèn)題咨詢(xún)，問(wèn)題是如何根據(jù)單據(jù)編號(hào)和物料長(zhǎng)代碼返回對(duì)應(yīng)的含稅數(shù)額。如下表：
其實(shí)這位學(xué)員的問(wèn)題就是如何實(shí)現(xiàn)多條件查詢(xún)。
下面通過(guò)一個(gè)實(shí)例跟大家分享一下常用的幾種多條件查詢(xún)方法。
下表是某電商公司的客戶(hù)投訴表，現(xiàn)在需要通過(guò)A表中的客戶(hù)姓名與地區(qū)兩個(gè)條件來(lái)查詢(xún)B表中的產(chǎn)品型號(hào)，返回到A表的E列中。
1.lookup函數(shù)
函數(shù)公式：=LOOKUP(1,0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19),$K$3:$K$19)
公式解析：首先通過(guò)A3單元格與B表I列數(shù)據(jù)做對(duì)比，同時(shí)用B3單元格與B表J列信息做對(duì)比。
在excel中如果兩個(gè)單元格對(duì)比，相等則返回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來(lái)除以0和1，由于分母不能為0，所以0/0返回的是錯(cuò)誤，0/1返回的結(jié)果為0。Lookup函數(shù)在查找的時(shí)候是忽略錯(cuò)誤的，所以只有數(shù)據(jù)運(yùn)算結(jié)果為1的公式滿(mǎn)足條件。
那么我們就很好理解0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19)的目的就是將正確結(jié)果用0表示，其他的變成錯(cuò)誤值，利用函數(shù)查找忽略錯(cuò)誤這個(gè)特點(diǎn)完成查找。
總結(jié)：本函數(shù)由于使用了二分法原理查找，所以如果數(shù)據(jù)量較大時(shí)運(yùn)算會(huì)很慢。
2.VLOOKUP函數(shù)
使用G2單元格在A列中查找，如果查找到對(duì)應(yīng)單元格則返回A列向右第二列的數(shù)據(jù)。簡(jiǎn)而言之：=VLOOKUP(查找什么，在哪查找，從條件所在列算起找到后返回對(duì)應(yīng)的第幾列數(shù)據(jù)，精確或模糊查找)。
那vlookup如何才能完成多條件查詢(xún)呢？。
還以客戶(hù)投訴表為例，按照姓名&地區(qū)來(lái)匹配產(chǎn)品型號(hào)返回到E里中。
其實(shí)我們是可以將A、B兩表中插入輔助列，將姓名和地區(qū)都合并到一個(gè)單元格中然后使用vlookup來(lái)完成。
但是插入2個(gè)輔助列后整個(gè)表列數(shù)發(fā)生變動(dòng)，在工作中往往單元格中有很多公式，如果列數(shù)發(fā)生變化將直接導(dǎo)致表格中函數(shù)公式運(yùn)算結(jié)果錯(cuò)誤。所以添加輔助列的方式雖然簡(jiǎn)單，但不是最好的方式。
那么不用輔助列如何才能完成多條件查詢(xún)呢？
首先我們查找值合并很簡(jiǎn)單，輸入函數(shù)vlookup時(shí)第一個(gè)參數(shù)可以寫(xiě)成A3&B3，即可將A3、B3兩個(gè)單元格內(nèi)容合并，作為查找值。
現(xiàn)在問(wèn)題查找區(qū)域也需要做合并。
如果把兩列內(nèi)容合并在一起，可輸入公式=H2:H19&I2:I19，按ctrl+shift+回車(chē)生成結(jié)果，然后下拉公式，這樣兩個(gè)條件就變成了一個(gè)。
接下來(lái)通過(guò)IF函數(shù)提取對(duì)應(yīng)的J列數(shù)據(jù)，可輸入公式
=IF({0,1},H2:H19&I2:I19,J2:J19)，按ctrl+shift+回車(chē)生成結(jié)果，然后下拉公式，{0,1}表示邏輯值{FALSE,TRUE}。
下面我們?cè)敿?xì)來(lái)解析一下:
首先在excel中0表示錯(cuò)誤，1以及其他所有數(shù)值表示正確。如下表示例：
通過(guò)上面的例子我看到如果IF判斷0則返回錯(cuò)誤，判斷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í)返回兩組數(shù)據(jù)，0對(duì)應(yīng)的是J2:J19，1對(duì)應(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中查找對(duì)應(yīng)J3:J20中的數(shù)據(jù)。因?yàn)楣街?/p>
IF({1,0},H3:H20&I3:I20,J3:J20)返回的順序是先返回H3:H20&I3:I20再返回J3:J20。
注意：很多人不明白為什么嵌套的時(shí)候IF第一參數(shù)又變成了{(lán)1,0}，因?yàn)檫@里我們需要返回的是H和I合并結(jié)果作為查找區(qū)域。PS：所有數(shù)組公式完成輸入后要使用數(shù)組三鍵ctrl+shift+ener來(lái)返回運(yùn)算結(jié)果！
這樣我們不用輔助列也能通過(guò)vlookup函數(shù)完成多條件查詢(xún)。
3.OFFSET+MATCH函數(shù)
下面舉例跟大家分享一下通過(guò)offset函數(shù)完成多條件查詢(xún)。
函數(shù)公式：
{=OFFSET($J$2,MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0),)}
公式解析：
完成多條件查詢(xún)第一步先要確定A表中姓名&地區(qū)合并后對(duì)應(yīng)在B表中姓名&地區(qū)的順序。這里我們通過(guò)MATCH來(lái)完成，我們用個(gè)簡(jiǎn)單的例子說(shuō)明。
=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表示精確查找。
確定順序后我們通過(guò)OFFSET函數(shù)以順序數(shù)據(jù)作為偏移行數(shù)返回對(duì)應(yīng)數(shù)值。
OFFSET函數(shù)的功能是以指定的單元格引用為參照系，通過(guò)給定偏移量得到新的引用。
返回的引用可以為一個(gè)單元格或區(qū)域。并可以指定返回的行數(shù)或列數(shù)。Reference 作為偏移量參照系的引用區(qū)域。Reference 必須為對(duì)單元格或相連單元格區(qū)域的引用；否則，函數(shù) OFFSET 返回錯(cuò)誤值#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為參照單元格，通過(guò)MATCH查找出來(lái)順序作為向下偏移的行數(shù)，偏移列數(shù)量省略表示不偏移，第三個(gè)、第四個(gè)參數(shù)省略表示只返回一個(gè)單元格區(qū)域。
下面我們來(lái)總結(jié)一下三種方式的利弊：LOOKUP函數(shù)使用過(guò)程中運(yùn)算較慢；VLOOKUP函數(shù)使用IF（{0,1}）數(shù)組公式，理解上存在一定難度；OFFSET+MATCH函數(shù)公式簡(jiǎn)單，可以作為首選方案。
＊＊＊＊部落窩教育－excel數(shù)據(jù)核對(duì)公式應(yīng)用＊＊＊＊
原創(chuàng)：龔春光/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）
聯(lián)系客服
