# VLOOKUP, Mismatch due to _ space

#### Sprucy

##### Board Regular
Hi everyone,

My VLOOKUP formula is correct, but I get N/A# because of the value of D8 contains 3 letters and 1 space "YYZ ", but my range shows "YYZ" without the space...

My Range contains 9500 entries and should not change all that much.
As for my "D8" cell value, it will change daily and the info is obtained from an external source.

So, I do I get a correct match?

=VLOOKUP(D8,'9497 Airports'!\$B\$2:\$K\$10000,7,FALSE)

Cheers,
Sprucy

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sprucy

Is it D8 that has the space?

=VLOOKUP(TRIM(D8),'9497 Airports'!\$B\$2:\$K\$10000,7,0)

You could also use something like this to trim all the strings within the data range. Run the macro first then create your formulas within Excel:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> TrimRange()
<SPAN style="color:#00007F">Dim</SPAN> WorkRange <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#00007F">Set</SPAN> WorkRange = Range("A1", ActiveCell.SpecialCells(xlCellTypeLastCell))

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> WorkRange
<SPAN style="color:#00007F">If</SPAN> Cell.Value = "" <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">GoTo</SPAN> Next1
<SPAN style="color:#00007F">ElseIf</SPAN> IsNumeric(Cell.Value) <SPAN style="color:#00007F">Then</SPAN>
Cell.Value = Cell.Value * 1
<SPAN style="color:#00007F">Else</SPAN>
Cell.Value = Trim(Cell.Value)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Next1:
Next Cell

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Hi Banker 1,

Your Macro work great at first, but I'm getting a error Bug 13 now.
I should of specified that Range is from D8 to D1000? (about 1000, different everyday...)

Sub TrimRange()
Dim WorkRange As Range

Set WorkRange = Range("d8", ActiveCell.SpecialCells(xlCellTypeLastCell))

For Each Cell In WorkRange
If Cell.Value = "" Then
GoTo Next1
ElseIf IsNumeric(Cell.Value) Then
Cell.Value = Cell.Value * 1
Else
Cell.Value = Trim(Cell.Value)
End If
Next1:
Next Cell

End Sub

Cheers,
Sprucy

I believe Error 13 is a mismatch error. What is the value of the cell where the code breaks when you get the error?

As far as the data range, if the cells you need to trim always fall in Column D, specifically the first value in D8 I would use this:

<font face=Courier New><SPAN style="color:#00007F">Set</SPAN> WorkRange = Range("D8:D" & Range("D65536").End(xlUp).Row)</FONT>

Hi Bannker1,

Sorry, never got back to say thanks.
Now my macro works perfectly and I'm happy to say that I've moved on to much more complex macro problems...

Cheers,
Sprucy

Replies
1
Views
335
Replies
8
Views
366
Replies
1
Views
674
Replies
0
Views
624
Replies
3
Views
549

1,203,123
Messages
6,053,638
Members
444,675
Latest member
FedElecQaEng

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back