VLOOKUP not returning desired result

hello hivemind,

I have a workbook which includes references to other sheets within the book, but for the purposes of simplicity I have added an example in one sheet here.

I have a cell which displays the tab name which is derived from a formula.

From there I want to use a Vlookup to show information in a given array, but when I use my formula I get a #NA error. I can reference a cell that has the information typed in it and it works, but I can't point the vlookup to the formula'd cell and for it to know I want it to use the result of the formula to determine the value I want to see for the lookup.

Does that make sense?

I'd be grateful if someone can offer a solution that will allow me to continue referencing the tab name so that the multiple tabs I have to manage can be automated please.

TIA,
Suzi

C1=MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255)+0

Now C1 is a number.

Thank you everyone!

Caracalla's reply fixed it by turning the cell from text into a value which the lookup can now read!

Thank you! Thank you! Thank you!

Originally Posted by JohnTopley

in C1

=--MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255)

converts to number

to access a worksheet

=VLOOKUP(C1,INDIRECT("'" & A1 & "'!A2:C100"),2,0)

A1 has sheet name

Thank you, I'll check that out too