VLOOKUP

Do you have the Analysis Toolpak installed?

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.

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

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

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