VLOOKUP returning #N/A. Lookup value not static number (formula derived number)

[muad'ebe]

I'm trying to do a VLOOKUP formula where the lookup value is also the tab number (number of tab located in cell Z2).

Cell Z2 has this formula in it =MID(@CELL("Filename",I7),SEARCH("]",@CELL("Filename",I7),1)+1,32)

The Z2 formula results in the cell being populated with a number starting with 1 and increasing by one with every added worksheet. (worksheet added with macro to create any size workbook copying a template as many times as necessary)

How can I get the vlookup to work as it's based on this cell value? If I enter a static number in cell Z2 the formula works. It doesn't work when the lookup value has the above formula in it.

I'm open to any other method to achieve a vlookup type result if it would work.

Thanks for reading and any help you may be able to offer.

 

[Grasor]

An #N/A error in a VLOOKUP() means the referenced value wasn't found in the array being searched.

It can get confused if the value being returned from your MID() isn't the same data type as what is being searched for in the array. If your VLOOKUP is looking for a specific number in a list of numbers, then the MID() function has to also return a number. You can't lookup number 'stored as text' in a list of numbers stored as numbers.

e.g. "1" isn't the same as 1. Another way is checking the logic: IF("1" = 1,TRUE,FALSE) = FALSE.

So check to make sure that isn't happening.

Other functions that can help you are

=ISNUMBER() determines if a value is considered a number in Excel.
=NUMBERVALUE() converts a number stored as text to a number.

You can wrap your NUMBERVALUE(VLOOKUP()) to convert a VLOOKUP returning a number as text to a number.

 

[Peter_SSs]

Adding to what @Grasor has said, in Z2 try adding this to your formula
=MID(@CELL("Filename",I7),SEARCH("]",@CELL("Filename",I7),1)+1,32)+0

 

[Grasor]

Adding to what @Grasor has said, in Z2 try adding this to your formula
=MID(@CELL("Filename",I7),SEARCH("]",@CELL("Filename",I7),1)+1,32)+0

Peter,

Even simpler-er, I've never actually done it like this but that does coerce a number stored as text to a number. Nice.

 

[Peter_SSs]

.. another option

=--RIGHT(SUBSTITUTE(CELL("Filename",I7),"]",REPT(" ",20)),20)

 

[muad'ebe]

Adding to what @Grasor has said, in Z2 try adding this to your formula
=MID(@CELL("Filename",I7),SEARCH("]",@CELL("Filename",I7),1)+1,32)+0

Thank you all for the solutions. I used adding the "+0" to the end of the existing formula and everything worked properly after that. Appreciate all the time you all spent responding.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.