How To Match Text Formatted Number ('010) With Absolute Number (10) In MS Excel ? (Vlookup Formula Trick)

Real time scenario: Mr. John has downloaded an Excel data from his internal ERP system. In one of the column the numbers are displaying as text ('010,'020,'030..). He want to lookup the those numbers from the master list by using Vlookup Formula. But the master list has the absolute numbers (10,20,30..). When he is trying to put the Vlookup Formula and Excel is resulting the error #N/A. This is because Excel can't match text formatted number ('010) with the absolute number (10).

What you do to use Vlookup Formula?

Most of the members do the following :

    "They convert the text numbers ('010,'020,'030..) into the numbers". Then excel simply match the absolute number with the exact absolute number. PROBLEM SOLVED !!!

No. The above is not the proper solution. Why because if we has one more master file which has text formatted numbers then how could you match this converted absolute number with the text formatted numbers in the master file.

The Proper Solution 

Use the below vlookup formula

=Vlookup (text_format_number + 0 , table_array , column_index_number , false) or,

=Vlookup (text_format_number * 1 , table_array , column_index_number, false)

Now, we can say that the problem solved.

How it works ?

Excel can not match the text formatted number ('010) with the absolute number (10).

But excel can do calculation with any of the text format number.

For example, if we add 2 to absolute number 10 then excel results 12 (2 +10 = 12). If we add 2 to text formatted number '010 then excel results the same (2 + '010 = 12). The important point here is the excel displays the result as absolute number.

Do you have any alternative solutions please let users know by giving comment below.