VLOOKUP formula returns #N/A when a match should be found

Problem:

List1 (A2:A4) and List2 (B2:B4) seem to contain the exact same values.
However, when using the VLOOKUP function to find the value in List2 matching each value in List1,
it returns #N/A for all the values.

Solution:
It is most likely that the formula did not find a match since the values are not 100% identical.
There might some invisible differences between them, such as different formatting, redundant spaces, etc.

For example:

-The text in cell A2 has a redundant space in the beginning of it. Applying the TRIM function on that cell, will remove the space.
-Cell A3 contains what appears to be the number 500. However, the number stored in that cell is, in fact, 500.2, and therefore,
it does not match the 500 in List2. Rounding the value in A3 to 0 decimal places, will allow VLOOKUP to find a match.
-Cell A4 contains the number 700, but it is formatted as text and is therefore different than the 700 in List2.
Applying the VALUE function on cell A4 will convert its format to number, which will enable VLOOKUP to find a match.
After adjusting the values in List1 (Corrected List1), all the matching values in List2 should be found.


List1____List2
Excel____500
500______700
700______Excel

If(d6>0,(ifd7>0,if(d8>0,if(d9>0,if(d10>0,count(d6,d7,d8,d9,d10,0)))