Vlookup help

[alexduy]

How come sometimes when I use Vlookup, for example =Vlookup(A1, B10:B10, 2, False), if the cell A1 has a formula in it,
it works sometimes, and other times it doesn't work. If I harcode cell A1, then it works. It's weird. Can someone explains to me why?

[alexduy]

I actually using index, match now. I have this formula =MATCH(T95, 'Sheet2'!$A$2:$A$18, 0).. I have a formula in cell T95, and it doesn't work. However, when I hard code the number, it returns the correct location of the array. So weird.

[martindwilson]

what's the formula in t95 and what is in 'Sheet2'!$A$2:$A$18

[Debraj Roy]

Just a blind shot...

Try..

=Vlookup(A1&"", B10:B10, 2, False)

and =Vlookup(A1+0, B10:B10, 2, False)

and let us know if any of these is working for you.. !!!

[alexduy]

It's just a left formula. It's weird because it's from a template I used from last month, and it worked just fine last month. I don't know why it doesn't work this month.

[martindwilson]

if its text it wont match a number see post#4
mind you
=Vlookup(A1&"", B10:B10, 2, False)will not work as its a single cell rang and doesn't have 2 columns!
=MATCH(T95+0,'Sheet2'!$A$2:$A$18, 0)
or
=MATCH(T95&"",'Sheet2'!$A$2:$A$18, 0) may work

[alexduy]

I found out why. It's because the formula cell "A1" is a text and the data in my vlookup table is not a text. I found out using ISTEXT function. Thank you anyways for your help guys.

[FDibbins]

Any time you use LEFT, RIGHT or MID, the result is always text, even if you are using it on a number.
=LEFT(123456,3) will give you 123, but that is text, not numeric, and will not be found in a list that contains the number 123. To convert it back to a value/nuber, a quick way is to use =--LEFT(123456,3)

[Kalithro]

You can wrap it in "Value(Ref)" as well.