Using Excel 2010

Using the first array-entered formula below works perfect to return a value from one of eight sheets contained in the Named Range "MySheets". (Minus Peo's name)


VLOOKUP(F1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),F1)0) ,0))&"'!A2:D200"),3,0) -Peo Sjoblom

I can return the sheet name along with the original return value by appending the first formula with this second one. Note that I now go to column 4 for the return value and all of column 4 on every sheet is that sheet's name, repeated 200times. Kinda messy.

&VLOOKUP(F1,INDIRECT("'"&INDEX(MySheets,MATCH(1 ,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),F1)0) ,0))&"'!A2:D200"),4,0)

This next formula returns a sheet's name, stripped of the full path. I can only make it work on the same sheet that the formula in entered. Is there a way to include this formula with the first vlookup formula so it will know to return the sheet name where the lookup value was found, be it sheet1 to sheet8? This would then unclutter the eight sheet of 200 names on each sheet.


MID(CELL("FILENAME",A1),FIND("]",CELL("FILENAME",A1))+1,LEN(CELL("FILENAME",A 1))-FIND("]",CELL("FILENAME",A1))) -Chip Pearson site

Thanks.

Regards,
Howard