Why doesn't vlookup find the match?

  • Thread starter Thread starter Jorge E. Jaramillo
  • Start date Start date
J

Jorge E. Jaramillo

I have a column with some 200 values and a large table with 4 columns in
which I need to find the valuesof the column. When I try to find matches
using =VLOOKUP(E2,A2:C2808,3,FALSE), sometimes it finds the matches and
sometimes it doesn't. I am positive that some of the values that the function
doesn't find, are exactly the same as ones on the table.

Is there any way to fix this and make it work properly? If I remove false
and change it for true or leave it blank, it produces results that can not be
trusted

Thank

Jorge E Jaramillo
 
If the ranges are right and it doesn't find it, it doesn't match, period.

Connect the two cells with a simple formula, eg =A3=E7 and see if you get a
TRUE or FALSE. I'll bet on FALSE.

Usual culprits are numbers stored as text, or vice versa - look identical,
but they are not.

If it works sometimes but not others then it sounds like you need to do some
data cleansing.

Regards
Ken..............................
 
I checked the data and changed the format of the columns where the data ois
and the column where the value to be searched for is.

The information to be searched is a 9 figure number that identifies
customers. I changed the format to Number on all the columns containing the
information but I still get the #N/A's.

In one of the cplumns there are many of those green triangles that indicate
that the format is not correct. Could this be the culprit? Any ideas on how
to fix it?

Thanks that with the help from this group's people, I am going in the right
direction

Jorge E Jaramillo
 
Could it be because the range is not absolute? Seems to me that assuming the
data is in correct format, that the A2:C2808 range needs to be absolute
$A$2:$C$2808.
 
=VLOOKUP(E2,A2:C2808,3,FALSE)

Try these variations (with the table array locked with $ signs)
[The 3 variations are ways to make the lookup values consistent
with what's in the lookup col A2:A2808]

=VLOOKUP(E2+0,$A$2:$C$2808,3,FALSE)
the: +0 will coerce any lookup values which are text nums to real nums, w/o
impacting its numerical value
(assuming all real nums in the lookup col A2:A2808)

=VLOOKUP(E2&"",$A$2:$C$2808,3,FALSE)
the: &"" bit will make the lookup values to text nums
(assuming all text nums in the lookup col A2:A2808)

=VLOOKUP(TEXT(E2,"0000"),$A$2:$C$2808,3,FALSE)
the TEXT function will pad leading zeros (if necess.) to the lookup values,
enabling consistency in matching with the text nums in the lookup col
A2:A2808. Adjust the "0000" part to suit

voila? celebrate success, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
 
Max

I did as you suggested but it brought #N/A's as results. Then I applied the
+ 0 trick to the column where the values should be checked in and IT
WORKED!!!!!!!!!


Thanks a million

Jorge Jaramillo
Max said:
=VLOOKUP(E2,A2:C2808,3,FALSE)
Click to expand...

Try these variations (with the table array locked with $ signs)
[The 3 variations are ways to make the lookup values consistent
with what's in the lookup col A2:A2808]

=VLOOKUP(E2+0,$A$2:$C$2808,3,FALSE)
the: +0 will coerce any lookup values which are text nums to real nums, w/o
impacting its numerical value
(assuming all real nums in the lookup col A2:A2808)

=VLOOKUP(E2&"",$A$2:$C$2808,3,FALSE)
the: &"" bit will make the lookup values to text nums
(assuming all text nums in the lookup col A2:A2808)

=VLOOKUP(TEXT(E2,"0000"),$A$2:$C$2808,3,FALSE)
the TEXT function will pad leading zeros (if necess.) to the lookup values,
enabling consistency in matching with the text nums in the lookup col
A2:A2808. Adjust the "0000" part to suit

voila? celebrate success, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
Click to expand...
 
THANK YOU THANK YOU THANK YOU

Max said:
=VLOOKUP(E2,A2:C2808,3,FALSE)
Click to expand...

Try these variations (with the table array locked with $ signs)
[The 3 variations are ways to make the lookup values consistent
with what's in the lookup col A2:A2808]

=VLOOKUP(E2+0,$A$2:$C$2808,3,FALSE)
the: +0 will coerce any lookup values which are text nums to real nums, w/o
impacting its numerical value
(assuming all real nums in the lookup col A2:A2808)

=VLOOKUP(E2&"",$A$2:$C$2808,3,FALSE)
the: &"" bit will make the lookup values to text nums
(assuming all text nums in the lookup col A2:A2808)

=VLOOKUP(TEXT(E2,"0000"),$A$2:$C$2808,3,FALSE)
the TEXT function will pad leading zeros (if necess.) to the lookup values,
enabling consistency in matching with the text nums in the lookup col
A2:A2808. Adjust the "0000" part to suit

voila? celebrate success, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
Jorge E. Jaramillo said:
I checked the data and changed the format of the columns where the data ois
and the column where the value to be searched for is.

The information to be searched is a 9 figure number that identifies
customers. I changed the format to Number on all the columns containing the
information but I still get the #N/A's.

In one of the cplumns there are many of those green triangles that indicate
that the format is not correct. Could this be the culprit? Any ideas on how
to fix it?

Thanks that with the help from this group's people, I am going in the right
direction

Jorge E Jaramillo
Click to expand...
Click to expand...
 
Thank you! I often use Vlookup to search for prices against product codes,
and am often frustrated by the formats being wrong, and not being able to
change them.

I use the method of converting to number by putting a number 1 into a spare
cell, copying that cell, then using 'paste special' - 'multiply' across the
data to convert it all to number. Which is useful some of the time.

However, as some product codes have leading zeros, these answers from Max
have given me new ways to deal with these problems.

The remaining question is, why doesn't Excel change the format of cells when
we change the format using 'Format cell' ? Odd, and frustrating!

Thanks Max.



Max said:
=VLOOKUP(E2,A2:C2808,3,FALSE)
Click to expand...

Try these variations (with the table array locked with $ signs)
[The 3 variations are ways to make the lookup values consistent
with what's in the lookup col A2:A2808]

=VLOOKUP(E2+0,$A$2:$C$2808,3,FALSE)
the: +0 will coerce any lookup values which are text nums to real nums, w/o
impacting its numerical value
(assuming all real nums in the lookup col A2:A2808)

=VLOOKUP(E2&"",$A$2:$C$2808,3,FALSE)
the: &"" bit will make the lookup values to text nums
(assuming all text nums in the lookup col A2:A2808)

=VLOOKUP(TEXT(E2,"0000"),$A$2:$C$2808,3,FALSE)
the TEXT function will pad leading zeros (if necess.) to the lookup values,
enabling consistency in matching with the text nums in the lookup col
A2:A2808. Adjust the "0000" part to suit

voila? celebrate success, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
Jorge E. Jaramillo said:
I checked the data and changed the format of the columns where the data ois
and the column where the value to be searched for is.

The information to be searched is a 9 figure number that identifies
customers. I changed the format to Number on all the columns containing the
information but I still get the #N/A's.

In one of the cplumns there are many of those green triangles that indicate
that the format is not correct. Could this be the culprit? Any ideas on how
to fix it?

Thanks that with the help from this group's people, I am going in the right
direction

Jorge E Jaramillo
Click to expand...
Click to expand...
 
I have a column with some 200 values and a large table with 4 columns in
which I need to find the valuesof the column. When I try to find matches
using =VLOOKUP(E2,A2:C2808,3,FALSE), sometimes it finds the matches and
sometimes it doesn't. I am positive that some of the values that the function
doesn't find, are exactly the same as ones on the table.

Is there any way to fix this and make it work properly? If I remove false
and change it for true or leave it blank, it produces results that can not be
trusted

Thank

Jorge E Jaramillo
Click to expand...

use the round formula to make it the same values. It is pretty stupid, but you can't see that it one of your values is off by just a slightest. no matter how many zero you place it out, it still wont show the rounding error.
 
Back
Top