Using VLookup, Indirect, and text formulas

[dasoidas]

Hi, 

I am having trouble with a formula meant to looks through sheets names by a date for a specific value. 
I've checked each part of the formula and it each piece appears to operate properly, but together it is not finding the cell with the matching value. 

The formula is: =VLOOKUP(TEXT($C3,"0"),INDIRECT(TEXT($B3,"MMYYYY")&"!A2:G65404"),1,FALSE)

C3 contains a number, 72. =Text($C3, "0") properly reformats the number to a string. 
B3 contains a date (Jan-1-21). =text($b3, "MMYYYY") properly reformats the date to a string 0120221. 
=INDIRECT(TEXT($B3,"MMYYYY")&"!A2") returns 72, which it should as '012021'!A2 contains the text 72.

But for whatever reason, when entered into the VLOOKUP formula it doesnt work. Yet if I do =VLOOKUP(72,'012021'!A2:G65404,1,FALSE) it works. 

Any ideas?

[#2 · Accepted solution]

@dasoidas 

You said 012021'!A2 contains the text 72. But you only get the result if you write the formula =VLOOKUP(72,'012021'!A2:G65404,1,FALSE).
That means 012021'!A2 value is not text format and it is the number format of 72. So your lookup value must be number format as below.
=VLOOKUP($C3,INDIRECT(TEXT($B3,"MMYYYY")&"!A2:G65404"),1,0)
(or)
=VLOOKUP(value($C3),INDIRECT(TEXT($B3,"MMYYYY")&"!A2:G65404"),1,0)

If not you need to ensure that the first column of table array must be in text format and your formula will surely work. But please note that if your are referring to the own sheet that you are writing the formula, your formula won't work as the circular reference may occur in the current sheet. Let's say your formula is in Column A to G (A2:G65404).

[#3]

@Starrysky1988 

The weird thing is, it is text. I've changed that entire sheet from text, to general, to number back to text several times. For the same reason you listed. And Ive tried with and without the text formula for each format to no avail. 

But.... value formula works! So problem solved. I'll figure whatever bug I missed another time. Thank you!