VLOOKUP

[Mike McLellan]

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?

[hans bal(nl)]

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?

[Mike McLellan]

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?

[Dave O]

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.

[Mike McLellan]

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.
>
>

[Dave O]

Do you have the Analysis Toolpak installed?

[Dave O]

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.

[Mike McLellan]

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.
>
>