代码之家 › 专栏 › 技术社区 › cpaone

Vlookup,但如果找到,请返回工作表名称

vlookup google-sheets-formula google-sheets

cpaone · 技术社区 · 2 年前

目前,这将返回“是”(在第6列)或“未找到”。如果为true,是否可以返回工作表名称?

提前谢谢。

 =IFERROR(VLOOKUP(A2,IMPORTRANGE("1eMzpzOJBDmrJfxqHTqEcllK8lFIN8C8fSkQUpZY1gJM", "Current Roster (Master)!A2:H"),6,false),
IFERROR(VLOOKUP(A2,IMPORTRANGE("179IscQOWpn7W1GPH5RSK4sc8zxUS2Ll3AoIwkxEPOX4", "Current Roster (Master)!A2:H"),6,false),
IFERROR(VLOOKUP(A2,IMPORTRANGE("1Xv0cjLgT_20ETSsWL031ybSvqG3onZlKMYXrLWiR1vc", "Current Roster (Master)!A2:H"),6,false),
IFERROR(VLOOKUP(A2,IMPORTRANGE("1Yjp-AVuuKYMNonUADc37h0CInN9cr7GCnxx0wIwoaY4", "Current Roster (Master)!A2:H"),6,false),
IFERROR(VLOOKUP(A2,IMPORTRANGE("1d1rGCzi8W975i__wZ-7RcuXMmWFXYjg5sZTceLDjgRw", "Current Roster (Master)!A2:H"),6,false),
IFERROR(VLOOKUP(A2,IMPORTRANGE("1ve8EAw3OMf_xRofRgRa6vBPGA3P72Gk8E77yrGFXyF8", "Current Roster (Master)!A2:H"),6,false),
IFERROR(VLOOKUP(A2,IMPORTRANGE("17-NKDd-WCO_XE0zXEwEcZ8CGHrB_cRIHg7wHnMnrIt4", "Current Roster (Master)!A2:H"),6,false),
IFERROR(VLOOKUP(A2,IMPORTRANGE("1bI2W5b3-WlgLe401enLpt9hqqjxvD41zMsU2gSGUsaQ", "Current Roster (Master)!A2:H"),6,false),
IFERROR(VLOOKUP(A2,IMPORTRANGE("1Zn75yae3XVhXxQEDLuG3gCZjkce2HDcxUaVDEfJmgck", "Current Roster (Master)!A2:H"),6,false), "Not Found")))))))))

0 回复 | 直到 2 年前

player0 2 年前

如果所有图纸名称相同,只需使用:

=SUBSTITUTE(
 IFERROR(VLOOKUP(A2, IMPORTRANGE("1eMzpzOJBDmrJfxqHTqEcllK8lFIN8C8fSkQUpZY1gJM", 
 "Current Roster (Master)!A2:H"), 6, ),
 IFERROR(VLOOKUP(A2, IMPORTRANGE("179IscQOWpn7W1GPH5RSK4sc8zxUS2Ll3AoIwkxEPOX4", 
 "Current Roster (Master)!A2:H"), 6, ),
 IFERROR(VLOOKUP(A2, IMPORTRANGE("1Xv0cjLgT_20ETSsWL031ybSvqG3onZlKMYXrLWiR1vc", 
 "Current Roster (Master)!A2:H"), 6, ),
 IFERROR(VLOOKUP(A2, IMPORTRANGE("1Yjp-AVuuKYMNonUADc37h0CInN9cr7GCnxx0wIwoaY4", 
 "Current Roster (Master)!A2:H"), 6, ),
 IFERROR(VLOOKUP(A2, IMPORTRANGE("1d1rGCzi8W975i__wZ-7RcuXMmWFXYjg5sZTceLDjgRw", 
 "Current Roster (Master)!A2:H"), 6, ),
 IFERROR(VLOOKUP(A2, IMPORTRANGE("1ve8EAw3OMf_xRofRgRa6vBPGA3P72Gk8E77yrGFXyF8", 
 "Current Roster (Master)!A2:H"), 6, ),
 IFERROR(VLOOKUP(A2, IMPORTRANGE("17-NKDd-WCO_XE0zXEwEcZ8CGHrB_cRIHg7wHnMnrIt4", 
 "Current Roster (Master)!A2:H"), 6, ),
 IFERROR(VLOOKUP(A2, IMPORTRANGE("1bI2W5b3-WlgLe401enLpt9hqqjxvD41zMsU2gSGUsaQ", 
 "Current Roster (Master)!A2:H"), 6, ),
 IFERROR(VLOOKUP(A2, IMPORTRANGE("1Zn75yae3XVhXxQEDLuG3gCZjkce2HDcxUaVDEfJmgck", 
 "Current Roster (Master)!A2:H"), 6, ), "Not Found"))))))))), 
 "Yes", "Current Roster (Master)")

更新:

=IFERROR(SUBSTITUTE(VLOOKUP(A2, IMPORTRANGE("1eMzpzOJBDmrJfxqHTqEcllK8lFIN8C8fSkQUpZY1gJM", "Current Roster (Master)!A2:H"), 6, ), "yes", "1st"),
 IFERROR(SUBSTITUTE(VLOOKUP(A2, IMPORTRANGE("179IscQOWpn7W1GPH5RSK4sc8zxUS2Ll3AoIwkxEPOX4", "Current Roster (Master)!A2:H"), 6, ), "yes", "2nd"),
 IFERROR(SUBSTITUTE(VLOOKUP(A2, IMPORTRANGE("1Xv0cjLgT_20ETSsWL031ybSvqG3onZlKMYXrLWiR1vc", "Current Roster (Master)!A2:H"), 6, ), "yes", "3rd"),
 IFERROR(SUBSTITUTE(VLOOKUP(A2, IMPORTRANGE("1Yjp-AVuuKYMNonUADc37h0CInN9cr7GCnxx0wIwoaY4", "Current Roster (Master)!A2:H"), 6, ), "yes", "xxx"),
 IFERROR(SUBSTITUTE(VLOOKUP(A2, IMPORTRANGE("1d1rGCzi8W975i__wZ-7RcuXMmWFXYjg5sZTceLDjgRw", "Current Roster (Master)!A2:H"), 6, ), "yes", "ccc"),
 IFERROR(SUBSTITUTE(VLOOKUP(A2, IMPORTRANGE("1ve8EAw3OMf_xRofRgRa6vBPGA3P72Gk8E77yrGFXyF8", "Current Roster (Master)!A2:H"), 6, ), "yes", "123"),
 IFERROR(SUBSTITUTE(VLOOKUP(A2, IMPORTRANGE("17-NKDd-WCO_XE0zXEwEcZ8CGHrB_cRIHg7wHnMnrIt4", "Current Roster (Master)!A2:H"), 6, ), "yes", "999"),
 IFERROR(SUBSTITUTE(VLOOKUP(A2, IMPORTRANGE("1bI2W5b3-WlgLe401enLpt9hqqjxvD41zMsU2gSGUsaQ", "Current Roster (Master)!A2:H"), 6, ), "yes", "9th"),
 IFERROR(SUBSTITUTE(VLOOKUP(A2, IMPORTRANGE("1Zn75yae3XVhXxQEDLuG3gCZjkce2HDcxUaVDEfJmgck", "Current Roster (Master)!A2:H"), 6, ), "yes", "â¥â¥â¥"), 
 "Not Found")))))))))