I am getting #N/A errors even when I have an apparently exact match in my
table array to the lookup value. I know that excel requires the formats to
be exactly the same and I can force the match if I copy the lookup value from
my table array and paste it into the worksheet. Is there an easy way to
"fix" my table array. The table array is a reference worksheet I have
created which has worked in the past.

are you working with dates?

"Ekazakoff" wrote in message
...
I am getting #N/A errors even when I have an apparently exact match in my
table array to the lookup value. I know that excel requires the formats
to
be exactly the same and I can force the match if I copy the lookup value
from
my table array and paste it into the worksheet. Is there an easy way to
"fix" my table array. The table array is a reference worksheet I have
created which has worked in the past.

If there are numbers involved it might be that either the lookup value or
the lookup column are text values

=ISTEXT(A1)

will return TRUE if a value is text thus you can check if that's the case

You could have invisible characters in the string, test with

=LEN(A1)

if that matches what you can count it is OK, if the resulting character
count is greater than what you can count you have invisible characters

To convert text numbers to number numbers, select the cell(s), do datatext
to columns and click finish or copy an empty cell, select the cell(s) in
question and do editpaste special and select add



--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com




"Ekazakoff" wrote in message
...
I am getting #N/A errors even when I have an apparently exact match in my
table array to the lookup value. I know that excel requires the formats
to
be exactly the same and I can force the match if I copy the lookup value
from
my table array and paste it into the worksheet. Is there an easy way to
"fix" my table array. The table array is a reference worksheet I have
created which has worked in the past.

I did find I have hidden characters. How do I get rid of them?
"Ekazakoff" wrote:

I am getting #N/A errors even when I have an apparently exact match in my
table array to the lookup value. I know that excel requires the formats to
be exactly the same and I can force the match if I copy the lookup value from
my table array and paste it into the worksheet. Is there an easy way to
"fix" my table array. The table array is a reference worksheet I have
created which has worked in the past.

You can run a macro

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


same site has instructions on how to install macros

if the extra character is a space you can use

=VLOOKUP(A2,TRIM(range),column_index,FALSE)

replace A2 with your lookup value and range with your lookup table

entered with ctrl + shift & enter


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com



"Ekazakoff" wrote in message
...
I did find I have hidden characters. How do I get rid of them?
"Ekazakoff" wrote:

I am getting #N/A errors even when I have an apparently exact match in my
table array to the lookup value. I know that excel requires the formats
to
be exactly the same and I can force the match if I copy the lookup value
from
my table array and paste it into the worksheet. Is there an easy way to
"fix" my table array. The table array is a reference worksheet I have
created which has worked in the past.