Hello, Im using an array formula to get the average for a specific symbol (F:F) on all/single spreadsheet(s) listed in formula, and it works. How can I use the array (or any other) formula to get the spreadsheet name containing the specific symbol? Thank you EFG35TECL30.11%TEST36FNGG-3.54%37WBREOX2.27%38FBGKX2.94%39SVSPX0.00%400No Data410No Data420No DataF35:F42F35=IFERROR(AVERAGE(IFERROR(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD","401k"}&"'!L9:L15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD","401k"}&"'!A9:A15"),E35),"")),"No Data")

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Spreadsheet name containing specific value

Thread starter gtd526
Start date Aug 6, 2023

gtd526

Well-known Member

Aug 6, 2023

Hello,
Im using an array formula to get the average for a specific symbol (F:F) on all/single spreadsheet(s) listed in formula, and it works.
How can I use the array (or any other) formula to get the spreadsheet name containing the specific symbol?
Thank you

The Whole Enchilada.xlsm

TECL

30.11%

TEST

FNGG

WBREOX

FBGKX

SVSPX

Capital Gains Tax

Cell Formulas
Range	Formula
F35:F42	F35	=IFERROR(AVERAGE(IFERROR(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD","401k"}&"'!L9:L15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD","401k"}&"'!A9:A15"),E35),"")),"No Data")
Press CTRL+SHIFT+ENTER to enter array formulas.