VLOOKUP Help

markster

Well-known Member
Joined
May 23, 2002
Messages
564
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,016
Office Version
  1. 365
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.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Do you have automatic recalculation on?

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

Forum statistics

Threads
1,144,611
Messages
5,725,308
Members
422,608
Latest member
bswg5882

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
Top