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

Question

目前，这将返回“是”（位于第六列）或“未找到”。如果 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")))))))))

Answer 1

如果所有工作表名称都相同，只需使用：

=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)")

---

update:

=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")))))))))