On Friday, December 21, 2012 9:55:53 AM UTC-8, wrote:
On Tuesday, November 20, 2012 3:25:16 AM UTC-5, wrote:

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)









Where you ever able to solve this? I am facing the same problem...



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

Yes, I have a working solution (workaround) which may not be suitable to all.
I procceded all possible return values on Sheet1 to Sheet5 with A, B, C, D, E respectively.

When a value is returned I used a worksheet change event to seperate the sheet code letter (A...E) to a cell and the "real" return value to another cell.

Then a simple vlookup using the A to E cell as the lookup value against sheet1 to sheet 5 table array to return the sheet name to the cell adjacent the "real" return value.

So you have the proper return value and the sheet name next to each other.

If anyone wants a copy, to look over and make any suggestions I'm glad to send you one. I believe my e-mail address is present somewhere in this thread as someone has already contacted me privaely.

Regards,
Howard