Using sheet name in VLOOKUP Lookup_Value

[Helky]

I have one sheet with a list of information. First cell is 1,2,3,4,... etc. Then I have multiple sheets looklike form and I need to get information from that first sheet. All other sheets are named 1, 2,3 etc. so sheet 1 should get information frome row 1.

I can get sheet name using formula =RIGHT(CELL("filename");LEN(CELL("filename"))-FIND("]";CELL("filename"))) but if I use that in VLOOKUP, for example =VLOOKUP(RIGHT(CELL("filename");LEN(CELL("filename"))-FIND("]";CELL("filename")));'Sheet1'!A:Z;7;FALSE) i recieve #N/A. What's wrong? If I use =VLOOKUP(A1;CELL("filename")));'Sheet1'!A:Z;7;FALSE) I recieve data correctly.

 

[StephenCrump]

Welcome to the Forum!

Your formula works OK for me. Are you sure that your list of sheet names in Sheet1 exactly matches each sheet name, e.g. no leading or trailing space characters?

By the way, with Excel 365, you can get the sheet name using a simpler formula: =TEXTAFTER(CELL("filename"),"]")

 

[Helky]

Thanks for that textaftercell =) . Now I actually changed numbers to letters a,b,c,d and using that it worked. Somehow those numbers did not work..

 

[StephenCrump]

Sorry, I should have realised. Your sheet name is "1", i.e. a string. If you are comparing to a numeric 1, then the VLOOKUP will return #N/A.

A1 below is text. B1 is numeric.

	A	B	C
1	1	1	FALSE
1

Cell Formulas
Range	Formula
A1	A1	=TEXTAFTER(CELL("filename"),"]")
C1	C1	=A1=B1