| Training | Upgrades | ||
| Support & Downloads | Computer Works Specials | Onsite Service | About Computer Works |
| Networking | Server | Peer | Wireless |
| Broadband Picks | Tip Sheets | www.geek2guru.com | Partners |
TROUBLESHOOTING VLOOKUP PROBLEMS
Trailing
spaces can wreak havoc on the results of your vertical lookups.
Here's a case study illustrating how to identify and fix those problems. A
user calls and gives this history: "My VLOOKUP formulas looked right, but
when I spot-checked the results, I had lots of #N/A errors.
And they weren't all caused by the same lookup value." To troubleshoot and
resolve this problem, eyeball the entries in the lookup table and compare
them to the values being looked up.
Are words spelled and punctuated consistently? (If not, fix the typos.) Next,
select one of the cells containing an entry that generated the #N/A error.
Press [F2] to edit the contents of the cell. If the insertion point (the flashing
cursor) isn't immediately to the right of the last visible character in the
cell, then this entry contains dirty data in the form of trailing spaces.
(If the cell generating the error isn't the culprit, it's probably the corresponding
entry in the lookup table itself.) So how do you clean up that dirty data
and remove the trailing spaces, either in the lookup range or in the lookup
table (or both)? One solution is to use the TRIM worksheet function.
Follow these
steps:
1. Insert a "dummy" column beside the column of dirty data.
2. Copy the formula =TRIM(cellref) down the dummy column, where cellref is
the first cell of the lookup range. The TRIM function removes all leading
and trailing spaces from the strings in the target cells.
3. Overwrite the corrupt lookup column with the trimmed data from the dummy
column. (To do so, select and copy the clean data. Click the cell at the top
of the old data, and go to Edit | Paste Special | Values.) Then you can discard
the dummy column.