Vlookup
Posted by REmus Garraway on January 11, 2002 7:58 AM
I have a vlookup function and the look up cell does not seem to find the data from the corresponding lookup table. If I go to the table and type in the key the look up cell returns the correct value (everything works well). All formats in both the look up cell and the look up table are the same (general) . This is an exact match , if I change it to true (not an exact match) everything works fine .
Here is the formula
=IF(ISERROR(VLOOKUP(A5,OL_Summ!$A$1:$B$279,2,FALSE))," ",(VLOOKUP(A5,OL_Summ!$A$1:$B$279,2,FALSE)))
Any suggestions ????
Appreciate your help !!!!
Posted by Mark W. on January 11, 2002 8:03 AM
Formatting isn't relevant to VLOOKUP -- its the
data format of the internally stored value that
matters! Compare the data type of value in A5
against the corresponding value in your lookup
table. Don't rely on your eyeballs use the
ISNUMBER() and/or ISTEXT() functions instead.
Posted by Mark W. on January 11, 2002 8:04 AM
Correction...
Posted by IML on January 11, 2002 8:26 AM
Outside your question, if your if statement is to avoid an N/A, I'd suggest changing your formula to
=IF(COUNTIF(OL_summ!$A$1:$A$279,a5),vlookup(a5,OL_summ!$A$1:$B$279,2,0),"")
As Mark said, you are almost certainly comparing text to numbers (which don't match). The easiest was to turn to text to numbers is to use text to columns under the data menu.
Alternatively, if your lookup value is a number and you table is text, you could modify the formula to
=IF(COUNTIF(OL_summ!$A$1:$A$279,text(a5,"0")),vlookup(text(a5,"0"),OL_summ!$A$1:$B$279,2,0),"")+0
If you lookup value is text and the tables are numbers, you could use:
=IF(COUNTIF(OL_summ!$A$1:$A$279,a5+0),vlookup(a5+0,OL_summ!$A$1:$B$279,2,0),"")
good luck
Posted by Re on January 11, 2002 9:34 AM
The data type in both instances is text , that is not the problem . I checked it unsing the istext , isnumber fx . Anymore suggestions ?
Posted by IML on January 11, 2002 9:44 AM
If the number you are looking up is calculated, try rounding it to the same number of places as your look up table.
Posted by Mark W. on January 11, 2002 10:38 AM
Check for trailing spaces (nt)
Posted by Remus Garraway on January 11, 2002 11:59 AM
It is not a rounding problem they are not calculated , and they are both text ??
How do I check for trailing blanks ?
Thanks,
Remus
Posted by Remus on January 11, 2002 12:01 PM
Re: Check for trailing spaces (nt)
How Do I check for trailing spaces ???
Posted by Chris D on January 11, 2002 12:14 PM
does this work :
=IF(ISERROR(VLOOKUP(A5,OL_Summ!$A$1:$B$279,2,FALSE)=true)," ",(VLOOKUP(A5,OL_Summ!$A$1:$B$279,2,FALSE))) ?
Chris
Posted by IML on January 11, 2002 12:14 PM
You could test by formula
right now if you do
=A5=OL_Summ!$A$1 (assuming your "match" is in A1)
should be false
If
=trim(A5)=trim(OL_Summ!$A$1)
is true, that's your problem.
You could also do this by seeing the the lengths are equal useing
=len()
Posted by Remus Garraway on January 11, 2002 12:44 PM
Thats great !!! Thanks a lot , but how do I make them equal now , the lookup table is 4 bytes and the other table is 5 bytes . Is there a way to make them equal??
remus
Posted by IML on January 11, 2002 1:01 PM
I'm not sure which has the added spaces...
If it is the value you are looking up, I'd use
=IF(COUNTIF(OL_summ!$A$1:$A$279,trim(A5)),VLOOKUP(trim(A5),OL_summ!$A$1:$B$279,2,0),"")
If your table contains the added spaces, I'd use
=IF(COUNTIF(OL_summ!$b$1:$b$279,A5),VLOOKUP(A5,OL_summ!$b$1:$c$279,2,0),"")
where you insert a new column "b" and use the formula =trim(a1) and copy it down.
If its both
=IF(COUNTIF(OL_summ!$b$1:$b$279,trim(A5)),VLOOKUP(trim(A5),OL_summ!$b$1:$c$279,2,0),"")
again with the new column b in your table...
Posted by Aladin Akyurek on January 11, 2002 1:07 PM
Re: Check for trailing spaces (nt)
ERR
Posted by Mark W. on January 11, 2002 1:35 PM
> how do I make them equal now
Use the Data | Text to Column... menu command to
re-parse your lookup values and the values in
the left-most column of of your lookup table.
