VLOOKUP Help

markster

Well-known Member
Joined
May 23, 2002
Messages
579
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I’m trying to lookup to hundreds of rows of names in column A. The problem is that there must be some background data that stopping the lookup from working. If I clear a cell with a name in totally and then re-type the lookup will work so there’s some corruption somewhere. I’ve tried copying and paste special to another clear cell but that doesn’t work. Anyone got any ideas how I can solve without clearing each cell and retyping each and every name in manually – there’s about 1000 in total !!!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
I'm guessing tha you have trailing spaces in your values.

Try using =TRIM(A1) and copy down. Then, either copy & paste the values of the formula over the original or base your VLOOKUP off the column with the TRIM function.
 
Upvote 0
Hi Mark

Have you considered the possibility of leading/trailing spaces (removable by the TRIM function) or possibly non-printing characters (removable using the CLEAN function)? Try using these formulas then copying and pasteSpecial the values back into your range.

Best regards

Richard
 
Upvote 0
Do you have automatic recalculation on?

Check through Tools|Options and Calculation tab. Automatic should be selected.
 
Upvote 0

Forum statistics

Threads
1,218,920
Messages
6,145,225
Members
450,603
Latest member
jpbegley

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?

Disable AdBlock

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
Back
Top