VLOOKUP Help

[markster]

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 !!!

 

[Oaktree]

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]

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]

Do you have automatic recalculation on?

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