finding exact matches using vlookup

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" <[email protected]> wrote in message
news:[email protected]...
>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 data>text
to columns and click finish or copy an empty cell, select the cell(s) in
question and do edit>paste special and select add



--


Regards,

Peo Sjoblom

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




"Ekazakoff" <[email protected]> wrote in message
news:[email protected]...
>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 data>text
to columns and click finish or copy an empty cell, select the cell(s) in
question and do edit>paste special and select add



--


Regards,

Peo Sjoblom

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




"Ekazakoff" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
>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.

Well, TRIM function might not work just good enought because it cleans
spaces but it doesnt clean HIDDEN characters! For that job there is CLEAN
function!
So your VLOOKUP should be little modify:
=VLOOKUP(A2,CLEAN(range),column_index,0)



"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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.
>
>

Well, TRIM function might not work just good enought because it cleans
spaces but it doesnt clean HIDDEN characters! For that job there is CLEAN
function!
So your VLOOKUP should be little modify:
=VLOOKUP(A2,CLEAN(range),column_index,0)



"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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.
>
>

The clean function won't remove invisible html characters which usually is
the culprit when trim does not work
thus I gave a link to a macro that will remove all obsolete characters
including the characters that CLEAN will remove

--


Regards,

Peo Sjoblom

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




"Sasa Stankovic" <[email protected]> wrote in message
news:[email protected]...
> Well, TRIM function might not work just good enought because it cleans
> spaces but it doesnt clean HIDDEN characters! For that job there is CLEAN
> function!
> So your VLOOKUP should be little modify:
> =VLOOKUP(A2,CLEAN(range),column_index,0)
>
>
>
> "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>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.
>>
>>
>
>

The clean function won't remove invisible html characters which usually is
the culprit when trim does not work
thus I gave a link to a macro that will remove all obsolete characters
including the characters that CLEAN will remove

--


Regards,

Peo Sjoblom

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




"Sasa Stankovic" <[email protected]> wrote in message
news:[email protected]...
> Well, TRIM function might not work just good enought because it cleans
> spaces but it doesnt clean HIDDEN characters! For that job there is CLEAN
> function!
> So your VLOOKUP should be little modify:
> =VLOOKUP(A2,CLEAN(range),column_index,0)
>
>
>
> "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>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.
>>
>>
>
>

There is one way to clean HTML characters from your data but you will
have to do it at a time for one column,
1)select the column in which you have HTML characters then press Ctrl+C
to copy the column.
2) then open (windows) NOTEPAD press Ctrl+V to paste your data in
NOTPAD.
3) then press Ctrl+H to find and replace the HTML characters.
4) just select one of the HTML character normaly it should be in the
end of every line copy it then paste it as value of Find what:
5) don't put any thing in Replace with: value press replace all .
6) press Ctrl+A to copy all and paste it back in excel column.
hope it will help you.

Regards,
Khan

Peo Sjoblom wrote:
> The clean function won't remove invisible html characters which usually is
> the culprit when trim does not work
> thus I gave a link to a macro that will remove all obsolete characters
> including the characters that CLEAN will remove
>
> --
>
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
>
>
>
>
> "Sasa Stankovic" <[email protected]> wrote in message
> news:[email protected]...
> > Well, TRIM function might not work just good enought because it cleans
> > spaces but it doesnt clean HIDDEN characters! For that job there is CLEAN
> > function!
> > So your VLOOKUP should be little modify:
> > =VLOOKUP(A2,CLEAN(range),column_index,0)
> >
> >
> >
> > "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >>>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.
> >>
> >>
> >
> >

There is one way to clean HTML characters from your data but you will
have to do it at a time for one column,
1)select the column in which you have HTML characters then press Ctrl+C
to copy the column.
2) then open (windows) NOTEPAD press Ctrl+V to paste your data in
NOTPAD.
3) then press Ctrl+H to find and replace the HTML characters.
4) just select one of the HTML character normaly it should be in the
end of every line copy it then paste it as value of Find what:
5) don't put any thing in Replace with: value press replace all .
6) press Ctrl+A to copy all and paste it back in excel column.
hope it will help you.

Regards,
Khan

Peo Sjoblom wrote:
> The clean function won't remove invisible html characters which usually is
> the culprit when trim does not work
> thus I gave a link to a macro that will remove all obsolete characters
> including the characters that CLEAN will remove
>
> --
>
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
>
>
>
>
> "Sasa Stankovic" <[email protected]> wrote in message
> news:[email protected]...
> > Well, TRIM function might not work just good enought because it cleans
> > spaces but it doesnt clean HIDDEN characters! For that job there is CLEAN
> > function!
> > So your VLOOKUP should be little modify:
> > =VLOOKUP(A2,CLEAN(range),column_index,0)
> >
> >
> >
> > "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >>>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.
> >>
> >>
> >
> >

There is one way to clean HTML characters from your data but you will
have to do it at a time for one column,
1)select the column in which you have HTML characters then press Ctrl+C


to copy the column.
2) then open (windows) NOTEPAD press Ctrl+V to paste your data in
NOTEPAD.
3) then press Ctrl+H to find and replace the HTML characters.
4) just select one of the HTML character normaly it should be in the
end of every line copy it then paste it as value of Find what:
5) don't put any thing in Replace with: value press replace all .
6) press Ctrl+A to copy all and paste it back in excel column.
Actully NOTEPAD can recognise those characters which excel can't.

Regards,
Khan

Peo Sjoblom wrote:
> The clean function won't remove invisible html characters which usually is
> the culprit when trim does not work
> thus I gave a link to a macro that will remove all obsolete characters
> including the characters that CLEAN will remove
>
> --
>
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
>
>
>
>
> "Sasa Stankovic" <[email protected]> wrote in message
> news:[email protected]...
> > Well, TRIM function might not work just good enought because it cleans
> > spaces but it doesnt clean HIDDEN characters! For that job there is CLEAN
> > function!
> > So your VLOOKUP should be little modify:
> > =VLOOKUP(A2,CLEAN(range),column_index,0)
> >
> >
> >
> > "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >>>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.
> >>
> >>
> >
> >

Hi try this!
1)select the column in which you have HTML characters then press Ctrl+C
to copy the column.
2) then open (windows) NOTEPAD press Ctrl+V to paste your data in
NOTEPAD.
3) then press Ctrl+H to find and replace the HTML characters.
4) just select one of the HTML character normaly it should be in the
end of every line copy it then paste it as value of Find what:
5) don't put any thing in Replace with: value press replace all .
6) press Ctrl+A to copy all and paste it back in excel column.
Actully NOTEPAD can recognise those characters which excel can't.

Regards,
Khan

Peo Sjoblom wrote:
> The clean function won't remove invisible html characters which usually is
> the culprit when trim does not work
> thus I gave a link to a macro that will remove all obsolete characters
> including the characters that CLEAN will remove
>
> --
>
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
>
>
>
>
> "Sasa Stankovic" <[email protected]> wrote in message
> news:[email protected]...
> > Well, TRIM function might not work just good enought because it cleans
> > spaces but it doesnt clean HIDDEN characters! For that job there is CLEAN
> > function!
> > So your VLOOKUP should be little modify:
> > =VLOOKUP(A2,CLEAN(range),column_index,0)
> >
> >
> >
> > "Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >>>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.
> >>
> >>
> >
> >