How to do a vlookup in Excel? Excel help 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:


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.

Become an Excel Pro: Join Our Course!

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.

Enroll Here

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.

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.

Frequently Asked Questions

How can I perform a VLOOKUP in Excel?
To do a VLOOKUP in Excel, enter the formula =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) into a cell. Replace lookup_value with the value you want to search for, table_array with the range of cells containing the data, col_index_num with the column number of the value you want to retrieve, and [range_lookup] with FALSE for an exact match or TRUE for an approximate match.
What steps are required to perform a VLOOKUP function in Excel for beginners looking to match data across different sheets?
To use VLOOKUP, enter the formula `=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])` into a cell. Replace `lookup_value` with the value you're searching for, `table_array` with the range where the data is located, `col_index_num` with the column number of the data to retrieve, and `[range_lookup]` with FALSE for an exact match or TRUE for an approximate match.

Complete 2019 Excel for Beginners in video


Yoann Bierling
About the author - Yoann Bierling
Yoann Bierling is a Web Publishing & Digital Consulting professional, making a global impact through expertise and innovation in technologies. Passionate about empowering individuals and organizations to thrive in the digital age, he is driven to deliver exceptional results and drive growth through educational content creation.

Become an Excel Pro: Join Our Course!

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.

Enroll Here

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.



Comments (6)

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

Leave a comment