HR Tech Wizard

Troubleshooting VLOOKUP: A Guide to Diagnosing and Fixing Common Errors

—

by

Tim Whitley
in , ,

VLOOKUP is a versatile tool that allows users to search for specific information in a table, returning the corresponding value from another column. However, as with any tool, it can occasionally lead to errors or confusion when not used correctly. In this blog post, we will discuss common VLOOKUP errors, offer step-by-step guidance on diagnosing and fixing these issues, and supply best practices for error-free VLOOKUP implementation.

Common VLOOKUP Errors and Their Causes

  • #N/A error: The most common VLOOKUP error occurs when the function cannot find a match for the lookup value in the specified range. This may be caused by misspelled or missing data, or by an incorrect range.
Here is an example from our sample employee data to pull email addresses. You can see in the formula bar at the top where I changed the name of the range (table_array) from Employees to Employee. This minor change caused the formula to error because Excel doesn’t know what range you are referring to. I would fix this by changing Employee to Employees in the table_array section of the formula.
In this example, I changed the lookup_value from a cell reference to an actual value but typed it incorrectly (Should be 110 for Camila, not 1100). With the extra zero, Excel can no longer find that value in the Employees worksheet. I would fix this by typing the correct lookup_value (110).
  • #REF! error: This error occurs when the column index number is set to a value greater than the number of columns in the specified range. Ensure that the column index number is within the range of available columns.
In this example, I changed the column number (col_index_num) from 5 to 6 which would normally work. However, the range (table_array) that Excel is looking in is only columns A through E – the 1st through 5th columns. To fix this, if I were trying to pull in the sixth column (Job Title in this dataset), then I would need to change the table_array to Employees!A:F to make sure the 6th column was included in the range.
  • #VALUE! error: This error typically occurs when the lookup value or the column index number is not a valid data type. Verify that the input values are of the correct data type.
In this example, I left off the column (col_index_num) piece of the function so Excel is interpreting FALSE as the desired column which isn’t a valid column number. I can fix this by typing the correct column number in between the table_array and the range_lookup.
  • Incorrect results: This issue arises when VLOOKUP returns a value other than the expected one. This is typically caused by a typo somewhere in the formula or the structure of the data itself.
In this example, I put A3 as the lookup value instead of A2. Therefore, it is pulling the value from the row below which, in this case, is Tamara Beck. If I copied this formula all the way down, all the data would be off by a row. (This is also why it is helpful to include identifying data like the name even though it is not essential to the formula. If I did not have the names, I wouldn’t be able to easily see that the email addresses are off.)
In this example, I put 4 in the col_index_num function which pulled the 4th column (in this case first name) instead of the one I wanted, email address.
This one is more involved and has to do with duplicates rows. In this version of the dataset, each employee has both a personal and work email address. VLOOKUP works by searching for the first row that matches the lookup value. In this case, Camila, Tamara, and Lesly had their personal email addresses on the first row and work email addresses on the second row in the Employee data set. There are several methods to fix this issue, and I will devote an entire post to dealing with duplicate data in the future.

Step-by-Step Guidance on Diagnosing and Fixing Issues

  • Double-check the syntax: Ensure that the VLOOKUP formula has the correct syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Make sure all the arguments are in the correct order and format.
  • Verify the lookup value: Check for any discrepancies in the lookup value, such as extra spaces, misspellings, or incorrect formatting. You can use the TRIM function to remove extra spaces from the lookup value.
  • Check the table array: Make sure that the table array includes the column holding the lookup values and the column from which the results should be returned. The lookup column should be the first column in the table array.
  • Assess the column index number: Confirm that the column index number corresponds to the correct column within the table array and is not greater than the total number of columns.

An effective way to check the inputs on any function in Excel is to use the Insert Function button. This can be found to the left of the toolbar (fx) or by pressing Shift + F3.

You get this box which shows you the values Excel is seeing for each function in the formula.

Best Practices for Error-Free VLOOKUP Implementation

  • Use named ranges: To reduce the likelihood of errors, consider using named ranges instead of cell references for the table_array argument. This makes the formula easier to understand and update.
  • Sort the data: When using approximate matches, ensure that the lookup column is sorted in ascending order to prevent incorrect results.
  • Utilize helper columns: If you need to lookup values based on multiple criteria, create a helper column that concatenates the criteria and use this new column as the lookup value.
  • Consider alternatives: If VLOOKUP is not suitable for your needs, explore alternative functions such as INDEX and MATCH, which offer greater flexibility and can perform both horizontal and vertical lookups. This function will be addressed in a future post.

In conclusion, by understanding common VLOOKUP errors and their causes, following step-by-step guidance for diagnosing and fixing issues, and implementing best practices for error-free VLOOKUP usage, you can harness the full power of this essential Excel function.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *