VLOOKUP
 

I am trying to use VLOOKUP to use a lookup value B5 to access an array to
return the value in column 2 of an array in another workbook. The expression
is as follows:

=VLOOKUP(B5,Temp!B5:D643,2,FALSE)

My problem is that, although looking at the values in B5 and Temp!B5 they
look identical, for some reason VLOOKUP is not recognising this. Also, if I
try =IF((B5=Temp!B7),"OK","Not OK") it comes up with 'Not OK'. Furthermore,
if I print out the ASCII character code for each character in each of the
strings, they are identical.

Can anyone help?

VLOOKUP
 

The problem lies in the fact that somehow Excel does not recognize the data
as identical. Do you maybe have leading or trailing spaces in your data ? For
instance if the text in B5 = "John" and the text in B7 = "John ", you wil
get this problem.

Hans

"Mike McLellan" wrote:

I am trying to use VLOOKUP to use a lookup value B5 to access an array to
return the value in column 2 of an array in another workbook. The expression
is as follows:

=VLOOKUP(B5,Temp!B5:D643,2,FALSE)

My problem is that, although looking at the values in B5 and Temp!B5 they
look identical, for some reason VLOOKUP is not recognising this. Also, if I
try =IF((B5=Temp!B7),"OK","Not OK") it comes up with 'Not OK'. Furthermore,
if I print out the ASCII character code for each character in each of the
strings, they are identical.

Can anyone help?

VLOOKUP
 

Most likely there is something different about the values in B5 and
Temp!B5 that is preventing an exact match. Is B5 formatted as a
number, and Temp!B5 is formatted as a string? It may be a leading or
trailing blank space, for instance, or one of the values may start with
an apostrophe(this usually happens when the value is imported from a
database or another application). You can test for this *using a
backed up copy of your data* copy copying the value in B5 to cell
Temp!B5. If the VLOOKUP returns the expected value then you'll need to
perform some secondary processing to remove the offending characters.

How did you print out the ascii code for each char in each string?
Just curious. Excel's CODE function, for instance, only returns the
ascii value for the first char in a cell.

VLOOKUP
 

Nothing like that I'm afraid. I've printed out the ASCII code for each
character in both strings, from 1 to the last character +1, to prove this -
the results are identical

"hans bal(nl)" wrote:

The problem lies in the fact that somehow Excel does not recognize the data
as identical. Do you maybe have leading or trailing spaces in your data ? For
instance if the text in B5 = "John" and the text in B7 = "John ", you wil
get this problem.

Hans

"Mike McLellan" wrote:

I am trying to use VLOOKUP to use a lookup value B5 to access an array to
return the value in column 2 of an array in another workbook. The expression
is as follows:

=VLOOKUP(B5,Temp!B5:D643,2,FALSE)

My problem is that, although looking at the values in B5 and Temp!B5 they
look identical, for some reason VLOOKUP is not recognising this. Also, if I
try =IF((B5=Temp!B7),"OK","Not OK") it comes up with 'Not OK'. Furthermore,
if I print out the ASCII character code for each character in each of the
strings, they are identical.

Can anyone help?

VLOOKUP
 

I did as you suggested and the VLOOKUP call is still returning an error

I used CODE(MID(x,y,1)) to print out all the ASCII codes

"Dave O" wrote:

Most likely there is something different about the values in B5 and
Temp!B5 that is preventing an exact match. Is B5 formatted as a
number, and Temp!B5 is formatted as a string? It may be a leading or
trailing blank space, for instance, or one of the values may start with
an apostrophe(this usually happens when the value is imported from a
database or another application). You can test for this *using a
backed up copy of your data* copy copying the value in B5 to cell
Temp!B5. If the VLOOKUP returns the expected value then you'll need to
perform some secondary processing to remove the offending characters.

How did you print out the ascii code for each char in each string?
Just curious. Excel's CODE function, for instance, only returns the
ascii value for the first char in a cell.

VLOOKUP
 

Do you have the Analysis Toolpak installed?

VLOOKUP
 

Your original post says
Also, if I try =IF((B5=Temp!B7),"OK","Not OK") it comes up with 'Not
OK'.

Assuming this is a typo and the IF should read B5=Temp!B5, this
suggests a formatting mismatch. If one is text and the other is
numeric, it will pass your ascii char code test and still fail the
VLOOKUP.

VLOOKUP
 

Yes - it was a typo

I've tried reformatting both ranges as text but it doesn't appear to make
any difference

"Dave O" wrote:

Your original post says
Also, if I try =IF((B5=Temp!B7),"OK","Not OK") it comes up with 'Not
OK'.

Assuming this is a typo and the IF should read B5=Temp!B5, this
suggests a formatting mismatch. If one is text and the other is
numeric, it will pass your ascii char code test and still fail the
VLOOKUP.

VLOOKUP
 

Simply reformatting the range doesn't help unless you go into each
cell, edit, and press Enter. This is a macro that will do that for
you: please try this on a BACKUP copy of your file to avoid the
possibility of data loss. Suppose you want to verify / convert that
data in column D is text. At the bottom of column D, below the data in
question, enter the word "stop" (no quotes) in a cell. Set your cell
pointer at the top of column D and run this macro. It temporarily
stores the entry in memory, clears the cell, formats the cell as text,
and rewrites the entry into that cell with no leading or trailing
spaces.

Remember- run this on a BACKUP copy of your file! This little macro
has worked properly for me countless times- don't be the first to
figure out it creates problems on your setup.

Sub TrueVal()
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Dim TrueVal as String

Do Until ActiveCell.Value = "stop"
TrueVal = Trim(ActiveCell.Value)
ActiveCell.Value = ""
Selection.NumberFormat = "@" 'set format to Text
ActiveCell.Value = Trim(TrueVal)
ActiveCell.Offset(1, 0).Select
Loop

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Calculate
End Sub

VLOOKUP
 

Thank you so much for this macro. I have been struggling with this for days.