V LOOKUP Question

[Alison]

Why is it when I am using the VLOOKUP function with
spreadsheets dervived from different database sources at
work(i.e. general ledger system, time reporting system)
the VLOOKUP values are sometimes N/A when in fact there is
an exact match. When I type the information over (exactly
the same), the data is then found. Does this have
anything to do with text formatting?

Thanks!
Alison

 

[Niek Otten]

Probably yes.

Check for spaces or special characters in the cells.
If the values are supposed to be numeric, they may not be recognized because
in fact they are text. To cure that, format an empty cell as number, enter
the number 1, Edit>Copy, select the cells under suspicion, Edit>Paste
Special, check Multiply

 

[Alan]

Probably, One way round it is to enter 1 into a blank cell and right click
Copy, highlight the whole range that's been imported or pasted in, and Paste
Special > Multiply. This formats the data from text (if in fact it is text)
to numbers.
If the data is coming from the web you may have spaces or the dreaded
CHR(160 ) which is a space but not one that Excel recognises as such.
To lose spaces, CHR(32), use =TRIM(your range) in another similar size
range, Paste Special > Values then paste back to overwrite the original.
To lose CHR(160), highlight the whole sheet, Edit > Replace, hold down the
Alt key and type 0160 on the numeric keypad, (not the Qwerty numbers) in the
Replace What box, (nothing will appear in the dialogue box), leave the
Replace With box empty and click Replace All
As with any actions such as this, back up the file first,
Regards,
Alan.

 

[Tim Zych]

I would run this. It should fix the gunk problem with data from other
systems.

Sub FixGunk()
'Macro by Tim Zych 7/17/03
Dim rngConst As Range, rngArea As Range
On Error Resume Next
Set rngConst = ActiveSheet.UsedRange.Cells. _
SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rngConst Is Nothing Then
For Each rngArea In rngConst.Areas
rngArea.Value = rngArea.Value
Next rngArea
End If
End Sub

To use it paste the macro in a module, activate the relevant sheet and run
it.