When applying vlookup on lists from different sources, you might easily end up not being able to match values.
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, …
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
Fig10 Compare vlookup difference
See above example.
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, Fig10)
Fig03 Excel format cells to textFig04 Select text cell formatFig05 Copy paste values in a text editorFig06 Copy paste values from text editor in Excel text formatted columnsFig07 Data pasted in excel as text in text columnsFig08 Paste second list as textFig09 Apply vlookup on both list pasted as text
