When MATCH and v/hLOOKUP functions *FAIL* to match (but they should)...
 

I have attempted to using MATCH & VLOOKUP to match search for matches.
For no apparent reason, I'm getting matches in some cases when I should
and in others the matches fails (#NA result).

If I look at the cell values compared, they SHOULD be matching.

There are no unprintable characters in the comparison cells.

Note also that I've set the parameters for MATCH and VLOOKUP correctly
to indicate unsorted list (0 & false, respectively).

I'm searching on 10 digit numbers. In some cases, I can get the
function to work correctly if I wrap the search value w/ the TRIM()
function. In others, I can get it to work if I divide the search value
by 1 ( match(a3/1,...) ). Rather nonsensical, but maybe that's a
tipoff to some of you?

Any ideas?

Holy Numbers-asText, Batman

The issue sure sounds as though your lookup values and/or your lookup table
is plagued with intermittent cases of what appears to be numbers actually
represented as text. Maybe there are leading or trailing spaces, just to
make matters worse, which is why the TRIM function sometimes helps. The
division by 1 coerces the text value to a numeric.

Try using VALUE(lookup value), see if that helps

Then, do what ever you can to ensure that your lookup table contains numeric
values - multiply them all by 1, for instance.

Good luck
Duke

" wrote:

I have attempted to using MATCH & VLOOKUP to match search for matches.
For no apparent reason, I'm getting matches in some cases when I should
and in others the matches fails (#NA result).

If I look at the cell values compared, they SHOULD be matching.

There are no unprintable characters in the comparison cells.

Note also that I've set the parameters for MATCH and VLOOKUP correctly
to indicate unsorted list (0 & false, respectively).

I'm searching on 10 digit numbers. In some cases, I can get the
function to work correctly if I wrap the search value w/ the TRIM()
function. In others, I can get it to work if I divide the search value
by 1 ( match(a3/1,...) ). Rather nonsensical, but maybe that's a
tipoff to some of you?

Any ideas?

Using a combination of trim(value)*1 seems to eliminate the problem, so
thanks.

Funny thing is data looks clean, both in Excel and in a text processor
that would let me see funny characters. Len() of values also doesn't
change after trimming, for example.