I have difficulties with downloading your file. It either can't be found, or is given as txt and/or doesn't contain any data in it.

Maybe you can upload again or a second example? Or am I the only one with these problems?

Sure. Thanks for the information, I didn´t know that. Tomorow I will attach again.

Hi all, (with attachment)

In attach Doubts - in sheets with Codes, when I did vlookup in many items appear N#A but if above code I click F2+CTRL HOME, the number appears. I formatted all codes with number, but the problem persists. Supposing if I will have 2000 lines, do F2+CTRL HOME is impossible, can you help me?

Hi Hava, this is such a typical Excel thing: you think you work with numbers, but you don't.

I see on most cells on sheet 'lista' in column B the warning that the value is formatted as text. Although you see it as numbers!

Another check you can do: for instance in cell E7 check: =ISNUMBER(B7) and it gives you 'not true' back.

Knowing that you can adjust your formula to let it work: in C4 the formula is =VLOOKUP(VALUE(B4),Sheet1!A:A,1,0) and copy it down.

This works perfect. Good luck!

Hi Hava

Try not to manually adjust the alignment of the column containing data. If Excel recognize a value as a number, it will automatically be right-aligned. Otherwise it will be left-aligned. Just expand the width of the column to see the alignment.

Just to tidy up Frans code a little (hope you don't mind Frans )

=TEXT(VLOOKUP(IFERROR(VALUE(B4),B4),Sheet1!A:A,1,0),"?")

Explanation:

IFERROR() will eliminate the error encountered in row 20 and 21. VALUE() can only convert "text value" to value. Otherwise it will generate a #VALUE! error.

TEXT() will allow the larger numbers to be displayed as text instead of 16E+13

Hope this helps.

Cheers

Sunny

Beautiful Sunny! Thanks for the improvement/complement. Didn't think about that issue. Cheers!

Many thanks for both. Sorry for the late answer.