#1
|
|||
|
|||
VLOOKUP not working properly with data digiting, only copy&paste
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! |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
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! |
#4
|
||||
|
||||
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 |
Tags |
vlookup |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Want to change Macro to copy and paste data to new sheet | Vortex69 | Excel Programming | 0 | 12-23-2014 09:53 PM |
copy and paste not working | Ellie | Word | 3 | 11-07-2013 02:23 PM |
How to Copy data from Outlook mail and Paste it in a Excel sheet? | padhu1989 | Outlook | 0 | 09-11-2012 04:07 AM |
Cannot copy ->paste table structure but data pastes ok | kangz | Word | 1 | 04-24-2012 04:05 AM |
Losing Text box data when copy and paste | Spay | Excel | 0 | 03-15-2011 07:18 AM |