Master Excel values vlookup - How to do vlookup

Quick links

When applying vlookup on lists from different sources, you might easily end up not being able to match values.

Typical example : SAP extract compared to your local text file, Excel list compared to an SAP extract, Excel list compared to a text list, ...

See example above. First list as an Excel, second list as a text file. When copying values from text file in Excel, the result is not correct :

the value "05678" was present in both lists, but vlookup didn't find it,

the value "19459" was not present in text file, but vlookup did find it.

See above example how to do vlookup in Excel.

On the left, values were copied paste directly in Excel.

On the right, values were copy pasted in Excel following below method, and gives the correct result.

In short, to master your vlookup, simply follow these steps :

Format new Excel destination columns as text (Fig03),

Copy data from 1st file in a text editor, for example Notepad or Notepad++ (Fig04, Fig05),

Paste data from 1st file in Excel text formatted columns (Fig06, Fig07),

Copy data from 2nd file in a text editor, for example Notepad or Notepad++,

Paste data from 2nd file in Excel text formatted columns (Fig08),

Apply vlookup - and voilà ! (Fig09, Fig03).

Make a perfect Excel vlookup

To make a perfect Excel vlookup, follow these steps:

- have list of values ready,

- format cells to text by pasting values in text editor and back in Excel,

- select a cell next to the first value to lookup,

- enter formula =vlookup("value to find", [list of values to search],1,0),

- apply vlookup on the whole list pasted as text.

This will ensure that all values are properly retrieved by the Excel vlookup, as it might otherwise not match values with different formats.

Similar articles

Comments (6)

Alfred Matthews, 2018-08-19
고맙습니다. 매우 유익합니다.
Kevin Morris, 2018-08-19
Gran información, gracias por compartir
WwjdStarBorn, 2018-08-19
Je vais l'essayer maintenant, merci pour le partage
Henerxes, 2018-08-19
यह मेरे लिए अच्छा काम करता है, आगे देखने की जरूरत नहीं है
pomenomz, 2018-08-19
من هم اکنون آن را امتحان می کنم، با تشکر برای به اشتراک گذاری
VagaiM, 2018-08-19
אני לא מאמין שסוף סוף מצאתי את הפתרון, זה היה סיוט במשך זמן רב, עכשיו נפתרה

Leave a comment