Do You Recognize the 3 Early Warning Signs of an Unexpected #N/A Error?

Every occasion you play a data analyst role you must not waste your time fixing unexpected problems. Your moral is affected each time you get an error while analyzing data.

The fact is that you usually get #N/A errors later in the process, just when it is time to analysis data and patterns.

I have found the symptoms of an imminent VLOOKUP #N/A error

By early detecting these signs, you will act on time to avoid affecting your stomach…

  1. The lookup table was not prepared by you. Keep doubtful each time you receive a flat table from an external source.  Most of the time, data entry guys are not supervised so you may get messy files. Before using the data, you must run a basic quality control. Once you inspect the list, be aware of spaces, wrong spelling, numbers stored as texts, dates as texts, etc
  2. You are not familiarized with the VLOOKUP arguments and possible errors. The function is easy to use and implement at first sight. However; if you adventure into writing the formula, you must know how each argument works… what happen if you omit something, how to enter the lookup_value, what does the fourth argument truly mean, etc. I struggled for a while until I found out that the function only searches the left index column of the backend table
  3. Exclusive Focus on writing the formula. If you write the formula without making any backend inspection you are prone to receive errors. Your backend index column may contain leading spaces, trailing spaces, wrong spelling, numbers stored as texts, dates not entered as serial numbers, etc. In summary; the VLOOKUP function won’t find the data because it is corrupted, mistyped, etc

One’s guts talk when doing things wrong; these guidelines were dictated by my guts over and over again. I hope you use them.

Let us know the signs your guts talk to you…