Vlookup but return sheet name if found

Question

Currently, this will return "Yes" (which is in the 6th column) or "Not Found". Would it be possible to return the sheet name if true?

Thanks in advance.

 =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

if all sheet names are same just use:

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