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.
Explore Your Business Potential: Discover Our Range of Transformative Courses Today!
Unveil the power of knowledge with our diverse array of courses, from Operational Procurement in S/4HANA to SEO essentials. Elevate your expertise and drive success in your business ventures.
Get your courseOn 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

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.
Explore Your Business Potential: Discover Our Range of Transformative Courses Today!
Unveil the power of knowledge with our diverse array of courses, from Operational Procurement in S/4HANA to SEO essentials. Elevate your expertise and drive success in your business ventures.
Get your course