VLOOKUP函数如何在多个工作表中查找相匹配的值
我们给出了基于在多个工作表给定列中匹配单个条件来返回值的解决方案。本文使用与之相同的示例，但是将匹配多个条件，并提供两个解决方案：一个是使用辅助列，另一个不使用辅助列。
下面是3个示例工作表：
图1：工作表Sheet1
图2：工作表Sheet2
图3：工作表Sheet3
示例要求从这3个工作表中从左至右查找，返回Colour列中为“Red”且“Year”列为“2012”对应的Amount列中的值，如下图4所示的第7行和第11行。
图4：主工作表Master
解决方案1：使用辅助列
可以适当修改上篇文章中给出的公式，使其可以处理这里的情形。首先在每个工作表数据区域的左侧插入一个辅助列，该列中的数据为连接要查找的两个列中数据。这样，获取值的数组公式（单元格C7）如下：
=VLOOKUP(A7&” ”&B7,INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT(“‘”&Sheets&”‘!A:A”),A7&” ”&B7)>0,0))&”‘!A1:D10″),4,0)
其中，Sheets是定义的名称：
名称：Sheets
引用位置：={“Sheet1″,”Sheet2″,”Sheet3”}
这个公式的运行原理与上文相同，可参见《Excel公式技巧16：使用VLOOKUP函数在多个工作表中查找相匹配的值（1）》。
解决方案2：不使用辅助列
首先定义两个名称。注意，在定义名称时，将活动单元格放置在工作表Master的第11行。
名称：Arry1
引用位置：=MATCH(TRUE,COUNTIFS(INDIRECT(“‘”&Sheets&”‘!B:B”),$A11,INDIRECT(“‘”&Sheets&”‘!C:C”),$B11)>0,0)
名称：Arry2
引用位置：=ROW(INDIRECT(“1:10”))-1
在单元格C11中的数组公式如下：
=INDEX(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!D1:D10”),MATCH(1,(T(OFFSET(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!B1”),Arry2,,,))=$A11)*(N(OFFSET(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!C1”),Arry2,,,))=$B11),0))
下面来看看公式是怎么运作的。首先看看名称Arry1：
=MATCH(TRUE,COUNTIFS(INDIRECT(“‘”&Sheets&”‘!B:B”),$A11,INDIRECT(“‘”&Sheets&”‘!C:C”),$B11)>0,0)
可以转换为：
=MATCH(TRUE,COUNTIFS(INDIRECT({“‘Sheet1’!B:B”,”‘Sheet2’!B:B”,”‘Sheet3’!B:B”}),”Red”,INDIRECT({“‘Sheet1’!C:C”,”‘Sheet2’!C:C”,”‘Sheet3’!C:C”}),2012)>0,0)
转换为：
=MATCH(TRUE,{0,0,1}>0,0)
结果为：
3
表明在工作表列表的第3个工作表（即Sheet3）中进行查找。
因此，在单元格C11的公式中的：
INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!D1:D10”)
转换为：
INDIRECT(“‘”&INDEX(Sheets,3)&”‘!D1:D10”)
转换为：
INDIRECT(“‘”&INDEX({“Sheet1″,”Sheet2″,”Sheet3″},3)&”‘!D1:D10”)
转换为：
INDIRECT(“‘”&”Sheet3″&”‘!D1:D10”)
转换为：
INDIRECT(“‘Sheet3’!D1:D10”)
结果为：
Sheet3!D1:D10
传递到INDEX函数中作为其参数array的值：
=INDEX(Sheet3!D1:D10,MATCH(1,(T(OFFSET(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!B1”),Arry2,,,))=$A11)*(N(OFFSET(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!C1”),Arry2,,,))=$B11),0))
同样，公式中的：
INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!B1”)
得到：
Sheet3!B1
公式中的：
INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!C1”)
得到：
Sheet3!C1
现在，单元格C3中的公式变为：
=INDEX(Sheet3!D1:D10,MATCH(1,(T(OFFSET(Sheet3!B1,Arry2,,,))=$A11)*(N(OFFSET(Sheet3!C1,Arry2,,,))=$B11),0))
由于这里的两个公式结构：
T(OFFSET(Sheet3!B1,Arry2,,,))=$A11
和
N(OFFSET(Sheet3!C1,Arry2,,,))=$B11
相似，因此只解释其中一个的工作原理。
先看看名称Arry2：
=ROW(INDIRECT(“1:10”))-1
由于将在三个工作表中执行查找的范围是从第1行到第10行，因此公式中使用了1:10。
上述公式转换为：
{1;2;3;4;5;6;7;8;9;10}-1
得到：
{0;1;2;3;4;5;6;7;8;9}
该数组被传递给OFFSET函数作为其rows参数，这样：
OFFSET(Sheet3!B1,Arry2,,,)
将会生成：
Sheet3!B1
Sheet3!B2
Sheet3!B3
…
Sheet3!B10
因此，公式：
T(OFFSET(Sheet3!B1,Arry2,,,))=$A11
转换为：
T(OFFSET(Sheet3!B1,{0,1,2,3,4,5,6,7,8,9},,,))=$A11
转换为：
T({Sheet3!B1,Sheet3!B2,Sheet3!B3,Sheet3!B4,Sheet3!B5,Sheet3!B6,Sheet3!B7,Sheet3!B8,Sheet3!B9,Sheet3!B10})=$A11
转换为：
{“Colour”,”Red”,”Blue”,”Blue”,”Red”,”Violet”,”Blue”,”Yellow”,”Green”,”Red”}=$A11
转换为：
{“Colour”,”Red”,”Blue”,”Blue”,”Red”,”Violet”,”Blue”,”Yellow”,”Green”,”Red”}=”Red”
得到：
{FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE}
注意，如果你在这里使用的是N函数：
N(OFFSET(Sheet3!B1,Arry2,,,))
其结果将为：
{0,0,0,0,0,0,0,0,0,0}
当然，也不能够单独只使用OFFSET函数：
OFFSET(Sheet3!B1,Arry2,,,)
其结果将为：
{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
同样地，公式中的：
N(OFFSET(Sheet3!C1,Arry2,,,))=$B11
转换为：
{0,2010,2010,2012,2012,2012,2012,2011,2014,2011}=2012
结果为：
{FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}
好了！现在可以将上面得到的中间结果放到主公式中：
=INDEX(Sheet3!D1:D10,MATCH(1,(T(OFFSET(Sheet3!B1,Arry2,,,))=$A11)*(N(OFFSET(Sheet3!C1,Arry2,,,))=$B11),0))
转换为：
=INDEX(Sheet3!D1:D10,MATCH(1,({FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE})*({FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}),0))
转换为：
=INDEX(Sheet3!D1:D10,MATCH(1,{0,0,0,0,1,0,0,0,0,0},0))
转换为：
=INDEX(Sheet3!D1:D10,5)
结果为
32
绛旓細vlookup鍑芥暟閫氬父鐢ㄤ簬灏嗕袱涓湁鐩稿悓椤圭殑琛ㄦ牸鍚堝苟锛屾瘮濡侫琛ㄦ湁璁惧銆佸瀷鍙峰垪锛岃孊琛ㄦ湁璁惧銆佺紪鍙枫佸崟浠蜂笁鍒楋紝鐜板湪闇瑕佹妸B琛ㄧ殑璁惧鍜岀紪鍙峰悎骞跺埌A琛ㄤ腑銆備竴銆丄琛ㄦ暟鎹 浜屻丅琛ㄦ暟鎹細涓夈佸悎骞跺悗鍏朵腑锛屽悎骞跺悗鏄剧ず鍥剧墖濡備笅锛1銆佸湪D3浣跨敤鐨勫叕寮忔槸=VLOOKUP(B3,Sheet2!B:D,2,0) 锛2銆丒3浣跨敤鐨勫叕寮忔槸=VLOOKUP...
绛旓細璇﹁闄勫浘鍚岃〃涓句緥 鏂规硶浜岋細Excel 2007浠ヤ笂鐗堟湰鍦⊿heet3鐨凚2杈撳叆浠ヤ笅鍏紡锛岀劧鍚庡悜涓嬪～鍏呭叕寮 =IFERROR(VLOOKUP(A2,Sheet1!A:B,2,0),VLOOKUP(A2,Sheet2!A:B,2,0))璇﹁闄勫浘鍚岃〃涓句緥 鏂规硶涓鍜屾柟娉曚簩锛岄兘鏄氳繃IF+ISERROR鍜孖FERROR鍑芥暟瀹归敊鎬э紝灏嗘棤娉曡繑鍥炵殑鐨勬敼鍚戠敤寮曠敤鍙︿竴涓〃鐨勬暟鎹
绛旓細4銆佸垏鎹㈠埌宸ヤ綔琛2锛屾嫋鍔ㄩ紶鏍囧叏閫夋暟鎹紱5銆佸垏鎹㈠埌宸ヤ綔琛1锛岃鍖归厤鐨勬槸宸ヤ綔琛2涓浜屽垪宸ヨ祫鐨勬暟鎹紝鎵浠ュ湪鏈熬杈 鍏モ滐紝2锛屸濆洖杞︼紱6銆佹垚鍔熷尮閰嶇涓涓暟鎹紝鍚戝彸涓嬭鎷栧姩榧犳爣銆7銆佸叏閮ㄥ尮閰嶅畬鎴愩傛敞鎰忎簨椤癸細VLOOKUP鍑芥暟鏄疎xcel涓殑涓涓旱鍚戞煡鎵惧嚱鏁帮紝鍙互鐢ㄦ潵鏍稿鏁版嵁锛澶氫釜琛ㄦ牸涔嬮棿蹇熷鍏ユ暟鎹瓑鍑芥暟...
绛旓細2. 鏁版嵁鎷嗗垎锛氬鏋滅敱浜庢煇绉嶅師鍥犳棤娉曞崌绾у埌鏂扮増鏈殑Excel锛屽彟涓涓彲琛岀殑瑙ｅ喅鏂规鏄皢鏁版嵁鎷嗗垎鍒澶氫釜宸ヤ綔琛ㄤ腑銆備緥濡傦紝濡傛灉浣犳湁涓涓寘鍚500鍒楁暟鎹殑宸ヤ綔琛紝浣犲彲浠ュ皢鍏舵媶鍒嗕负涓や釜宸ヤ綔琛紝绗竴涓伐浣滆〃鍖呭惈鍓256鍒楋紝绗簩涓伐浣滆〃鍖呭惈鍓╀綑鐨勫垪銆傜劧鍚庯紝浣犲彲浠ュ湪姣忎釜宸ヤ綔琛ㄤ笂鍒嗗埆浣跨敤VLOOKUP鍑芥暟锛屽厛鍦ㄤ竴涓伐浣...
绛旓細鍙互銆1銆佽繖鏄〃鏍1鐨勬暟鎹2銆佽鍦ㄨ〃鏍2涓娇鐢vlookup鍑芥暟鍖归厤鍑哄勾榫勩3銆佽緭鍏ュ叕寮=VLOOKUP(A18,Sheet1!$A$19:$B$23,2,0)銆4銆佽緭鍏ュ叕寮忓悗锛屾寜鍥炶溅閿嵆鍙緱鍒板搴旂殑骞撮緞銆5銆佷笅鎷夊鍒跺叕寮忓嵆鍙
绛旓細鍒涘缓杈呭姪鍒楋細濡傛灉涓や釜琛ㄦ牸娌℃湁鍏卞悓鐨勫敮涓鏍囪瘑绗︼紙濡侷D锛夛紝浣犲彲鑳介渶瑕佸垱寤轰竴涓緟鍔╁垪锛岃繖涓緟鍔╁垪鍦ㄤ袱涓〃鏍间腑閮藉簲璇ュ瓨鍦紝骞朵笖鍏锋湁鍞竴鍊笺備娇鐢╒LOOKUP鏌ユ壘锛氬湪涓涓〃鏍间腑浣跨敤VLOOKUP鍑芥暟锛屽皢杈呭姪鍒椾綔涓簂ookup_value锛岀劧鍚庡湪鍙︿竴涓〃鏍肩殑鐩稿簲鍒椾腑鏌ユ壘骞惰繑鍥炵粨鏋溿傚鐞澶氫釜鍖归厤锛氬鏋滃瓨鍦ㄥ涓尮閰嶇殑鎯呭喌锛...
绛旓細VLOOKUP鍑芥暟涓鑸竴娆″彧鑳借繑鍥炰竴涓粨鏋滐紝浠婂ぉ锛屽皬缂栧氨鏁欏ぇ瀹濡備綍閫氳繃杈呭姪鍒楃殑鏂规硶浣縑LOOKUP鍑芥暟鏌ヨ涓涓硷紝杩斿洖杩欎釜鍊煎搴旂殑澶氫釜缁撴灉銆侲xcel涓vlookup鍑芥暟鏌ヨ涓涓艰繑鍥炲涓肩殑鏂规硶濡備笅锛氬湪鍘熷鏁版嵁涓瑼鍒楁湁澶氫釜鈥滃紶涓変赴鈥濓紝闇瑕佹彁鍙栨瘡涓滃紶涓変赴鈥濆搴旂殑鈥滃伐鍙封濄備竴鑸儏鍐电殑VLOOKUP鍙兘杩斿洖绗竴涓硷紝濡備笅锛...
绛旓細lookup_value鏄鏌ユ壘鐨勫硷紱table_array鏄鏌ユ壘鐨勫尯鍩燂紱col_index_num鏄繑鍥炴暟鎹湪鍖哄煙鐨勭鍑犲垪鏁帮紱range_lookup鏄ā绯婂尮閰嶏紝鍐橳RUE鎴朏ALSE 1.绗竴姝ヨ鎶婁袱涓尮閰嶇殑宸ヤ綔绨垮噯澶囧ソ锛屽鍥撅紝瑕佹妸琛2鍑虹幇鐨勯殢鏈哄鍚嶅搴旂殑鍦扮悊鎴愮哗鍦ㄨ〃1涓壘鍑烘潵鍖归厤锛岃揪鍒颁竴瀵逛竴瀵瑰簲銆2.绗簩姝ュ啓vlookup 鍑芥暟(=VLOOKUP(...
绛旓細VLOOKUP鍑芥暟鏄疎xcel涓殑涓涓旱鍚戞煡鎵惧嚱鏁帮紝瀹冧笌LOOKUP鍑芥暟鍜孒LOOKUP鍑芥暟灞炰簬涓绫诲嚱鏁帮紝鍦ㄥ伐浣滀腑閮芥湁骞挎硾搴旂敤锛屼緥濡傚彲浠ョ敤鏉ユ牳瀵规暟鎹紝澶氫釜琛ㄦ牸涔嬮棿蹇熷鍏ユ暟鎹瓑鍑芥暟鍔熻兘銆傚姛鑳芥槸鎸夊垪鏌ユ壘锛屾渶缁堣繑鍥炶鍒楁墍闇鏌ヨ鍒楀簭鎵瀵瑰簲鐨勫硷紱涓庝箣瀵瑰簲鐨凥LOOKUP鏄寜琛屾煡鎵剧殑銆傚弬鑰冭祫鏂欙細VLOOKUP鍑芥暟 鐧惧害鐧剧 ...
绛旓細VLOOKUP鍑芥暟鏄疎xcel涓殑鍨傜洿鏌ユ壘鍑芥暟銆傚畠涓庢煡鎵惧嚱鏁板拰HLOOKUP鍑芥暟灞炰簬鍚屼竴绫汇傚畠鍦ㄥ伐浣滀腑寰楀埌浜嗗箍娉涚殑搴旂敤锛屼緥濡傦紝瀹冨彲浠ョ敤浜庢鏌ユ暟鎹紝鍦ㄥ涓琛ㄤ箣闂村揩閫熷鍏ユ暟鎹傚叧浜嶸LOOKUP鍑芥暟寰堝灏忎紮浼鎬庝箞瀛︿範閮藉涓嶄細鍏朵娇鐢ㄦ柟娉曪紝鎺ヤ笅鏉ユ垜浠氨灏嗘暣鐞嗗叏闈㈢殑VLOOKUP鍑芥暟浣跨敤鏂规硶鍒嗕韩缁欏ぇ瀹讹紝甯屾湜甯姪鍒板ぇ瀹跺揩閫熷浼殈Excel...
