In the file attached you can see how the function vlookup (in Italian 'CERCA.VERT') doesn't work if the desired value is inserted manually in the cell.


It works instead if inserting with a copy&paste from another specific cell.
The translation of text in the file is:
"As you can see, even if the text '84100' is correctly digited in B2, D1 doesn't return 10,9 as it should.
Instead, copying and pasting the value of A1 in B2 the function works properly"

I checked the exact value and it's correct; also cell formatting should be ok.
I suppose it's something that has to do with the data type but I absolutely don't know what.

Thanks for help!

Attached Files

Test CERCA.VERT.xlsx (10.3 KB, 10 views)

Buon giorno,
As far as I can see, everything is OK in the sample you posted. ( I am using OpenOffice though and this might explain it)
BUT, all the values in col A are text values ( preceded by an apostroph) while the values in col B are numbers. You can verify this with the TYPE() function eventually.

It so happens that when XL compares two contents it first compares their TYPE. If they are not the same, XL returns FALSE

If you need to keep text values in col A as is try =VLOOKUP(value(A1),b1:C1000,2,0)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Dear Pecoflyer,
you're answer is just perfect.

Yes, probably OpenOffice doesn't care about the type, while Office does.
I edited the formula as you suggested and now it works.

Thank you very very much!

Con piacere !

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post