Forum Discussion
Using VLookup, Indirect, and text formulas
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).
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).
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!
