How to do a vlookup in Excel? Excel help vlookup

How to do a vlookup in Excel? Excel help vlookup

Compare lists in Excel with a perfect vlookup

When applying vlookup on lists from different sources, you might easily end up not being able to match values. This guide on Excel help vlookup will show you how to solve common problems on why is vlookup not working such as:

  • vlookup in excel not working,

  • vlookup not working on text,

  • vlookup different formats,

  • excel vlookup not working on text.

Vlookup not working on text in Excel

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

Using different sources usually is the reason why vlookup in Excel is not working on text, because of different formats used to compare lists in Excel, therefore leading to incorrect results. The solution is to start by putting all your data in a similar format.

Vlookup different formats issue

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 because the Vlookup is applied to compare lists in Excel with different formats:

  • 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 pastes directly in Excel to compare the two lists between each other with a vlookup.

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

Excel help vlookup to solve different formats issue

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

Compare lists in Excel with perfect vlookup

To make a perfect list comparison, follow these Excel help vlookup 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.

Complete 2019 Excel for Beginners in video

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