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.

Master Excel values vlookup - How to do vlookup : Lists from different sources
Lists from different sources

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

Master Excel values vlookup - How to do vlookup : Vlookup not working on lists from different source
Vlookup not working on lists from different source

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.

Master Excel values vlookup - How to do vlookup : Fig03 Compare vlookup difference
Fig03 Compare vlookup difference

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).

Master Excel values vlookup - How to do vlookup : Fig03 Excel format cells to text
Fig03 Excel format cells to text
Master Excel values vlookup - How to do vlookup : Fig04 Select text cell format
Fig04 Select text cell format
Master Excel values vlookup - How to do vlookup : Fig05 Copy paste values in a text editor
Fig05 Copy paste values in a text editor
Master Excel values vlookup - How to do vlookup : Fig06 Copy paste values from text editor in Excel text formatted columns
Fig06 Copy paste values from text editor in Excel text formatted columns
Master Excel values vlookup - How to do vlookup : Fig07 Data pasted in excel as text in text columns
Fig07 Data pasted in excel as text in text columns
Master Excel values vlookup - How to do vlookup : Fig08 Paste second list as text
Fig08 Paste second list as text
Master Excel values vlookup - How to do vlookup : Fig09 Apply vlookup on both list pasted as text
Fig09 Apply vlookup on both list pasted as text

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