In the past I was able down load a number from a data file (credit card
transaction numbers for internal audit part of my job) I would have to change
the number to a value for the vlookup to work properly. 15 character eg
999563256923654 would be translated to come out as 9.99563E+14 WHEN CONVERTED
TO A VALUE.
The problem I have now that the transaction numbers have increased one
digit to 16 characters transferring it to a value is rounding up or down the
last number to eg 10000000000001656 conveting to a value (1e+16) and when
hover over the cell would find that the last number (6) has been rounded up
to 0 this is very frustrating as it is not aligning with the right credit
card transaction. Can someone please explain why this is happening. Is it
something in my settings in excel. Your help will be appreciated. Arthur

Excel only keeps track of 15 significant digits.

Maybe you can bring the field in as Text -- but that means that your table will
have to have those matching values as Text, too.

Arthur Moraitis wrote:

In the past I was able down load a number from a data file (credit card
transaction numbers for internal audit part of my job) I would have to change
the number to a value for the vlookup to work properly. 15 character eg
999563256923654 would be translated to come out as 9.99563E+14 WHEN CONVERTED
TO A VALUE.
The problem I have now that the transaction numbers have increased one
digit to 16 characters transferring it to a value is rounding up or down the
last number to eg 10000000000001656 conveting to a value (1e+16) and when
hover over the cell would find that the last number (6) has been rounded up
to 0 this is very frustrating as it is not aligning with the right credit
card transaction. Can someone please explain why this is happening. Is it
something in my settings in excel. Your help will be appreciated. Arthur

--

Dave Peterson

Once the transaction number exceeds 15 digits, you should you should stop
trying to convert to a value and starting treating the digits as Text. This
will allow the VLOOKUP() to function poperly.

For example, if D1 thru E5 contains:

6841145576479986 Larry
4170432082457810 Moe
7321426544703675 Shep
4996312194002342 Curly
2963555685111655 Wilber

and if A1 contains:

4170432082457810

then
=VLOOKUP(A1,D1:E5,2,FALSE)
will correctly display Moe
--
Gary''s Student - gsnu201002


"Arthur Moraitis" wrote:

In the past I was able down load a number from a data file (credit card
transaction numbers for internal audit part of my job) I would have to change
the number to a value for the vlookup to work properly. 15 character eg
999563256923654 would be translated to come out as 9.99563E+14 WHEN CONVERTED
TO A VALUE.
The problem I have now that the transaction numbers have increased one
digit to 16 characters transferring it to a value is rounding up or down the
last number to eg 10000000000001656 conveting to a value (1e+16) and when
hover over the cell would find that the last number (6) has been rounded up
to 0 this is very frustrating as it is not aligning with the right credit
card transaction. Can someone please explain why this is happening. Is it
something in my settings in excel. Your help will be appreciated. Arthur

try by inserting a single quote (') before the big numbers, excel treat them as text. Use the same single quote concept at both source and destination.

all the best