1. Sort the First Column
In the VLOOKUP approximate search, where the range_lookup is TRUE or left out, the first column needs to be sorted alphabetically or numerically from smallest to largest. Otherwise, the returning value might be something wrong.
For example, when using VLOOKUP to approximately match the score in column A with the scores in column E, the returning results are not correct if column E is not sorted. 89 in column A should return to A- but returns to F if column E is not sorted properly.
When this error is noticed, the solution is to sort the table_array based on the first column from smallest to largest.
2. #N/A Error
In exact match (the range_lookup is FALSE), the #N/A Error value indicates that the exact number isn't found. For example, in the following table, the value of cell A2 is 78 which cannot find in column E, so the result returns #N/A.
In approximate match (the range_lookup is TRUE), the #N/A Error value indicates that the lookup_value is smaller than the smallest value in the first column of the table_array.
For example, in the following table, the value of cell A13 is -23, which is smaller than the smallest value (0) in column E. Because approximate match will return the next largest value but less than the look_up value, there is no value in column E that is less than -23.
3. #REF! Error
The #REF! Error appears when the col_index_num is greater than the number of columns in table-array. For example, in the following table, search value in column A from column F to return the value in column G. The table-array has 2 columns, but the col_index_num in the formula is 4, which is larger than 2. The result returns #REF!
When this error happens, you may need to check the col_index_num and change back to the col_index_num you need to return.
4. #VALUE! Error
The #VALUE! Error will show up when the col_index_num is less than 1.
For example, matching value 60 (cell A2) in column E with the col_index_num 0, which is smaller than 1, the result will return #VALUE!.
5. #NAME? Error
To lookup a text value, make sure you use quotation marks around the text in the formula, otherwise, you will get the #NAME? Error.
For example, to search the name of "Jackson" from column F, the correct formula should be
=VLOOKUP("Jackson", $F$2:$H$11,2,FALSE). If the quotation marks are missed =VLOOKUP(Jackson, $F$2:$H$11,2, FALSE), you will get #NAME? Error.
