Excel’s COUNTIF function is used to return the number of cells which match given criteria. The VLOOKUP function returns a cell on a row where a given value is found.
For the VLOOKUP function, the value being looked up must be exactly the same (without case sensitivity) as it’s corresponding value in the range being looked into. By contrast, the COUNTIF function allows you to specify the criteria for matching data, (eg ”apples”, or “>55”). When Excel comes to evaluate your criteria, it allows for a variety of cases where the cells may differ slightly. Once such case is when the value you are looking up is text (prefixed by a ‘ ), and the range you’re looking into is numeric. The VLOOKUP function does not check for such cases.
The VLOOKUP wizard uses the COUNTIF function to evaluate your lookup. It also uses the “wildcard” functionality that COUNTIF allows to see if the data you are looking up has any spaces either side of it, which are frequent causes of VLOOKUP errors.