December 29, 2016
Hi all,
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?
VIP
April 21, 2015
December 29, 2016
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?
VIP
April 21, 2015
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!
VIP
Trusted Members
June 25, 2016
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
1 Guest(s)