Vlookup function doesn't work on identical text

Hi guys,

I have an example for same text on which vlookup doesn't recognize it in the attached file.

The vlookup formula is in cell B4. As you can see, it returns #N/A.

If I try copying cell A1 to A4 or vice versa, the vlooup formula does display the correct value.

What can be the reason for that?

Thank you,

Moty

The value in A4 is a "number stored as text", not a real number.
But the value in A1 IS a real number.
So they are not identicle.

Try copying any blank cell
Right click on A4
Paste Special - Values - Add - OK

Thank you all very much for your answers.

How come that even after using the "Format Painter" which changes the cells to become with the same format (e.g. 'Number' format), vlookup formula still doesn't work?

How can I change a 'text' format value to a 'number' format value if I can't use the "Format cells" dialog box for that?


Thanks again,

Moty

Applying a format to a cell doesn't alter/modify the value in the cell.
It only changes the 'appearance' of the value in the cell.

Observe..
Type 1.27 in a cell
Format that cell as a Number with 1 Decimal.
Now highlight that cell and look at the cell, it shows 1.3
But look in the formula bar, it still shows 1.27

Thank you Jonmo.

So how can I change a 'text' format value to a 'number' format value if I can't use the "Format cells" dialog box for that?

you don't have to change the actual format of a cell and still can reference it in a different format. For example, if cell a2 have a text value of 01, when you reference to a2, you can reference to it as a number by simply add value(a2). I hope it make sense.

I see now.

Thank you very much Ron and everyone else.