7 Easy Things to Do Before You Write a VLOOKUP Formula

If you are like the majority of Excel users, you feel a great sense of achievement when your VLOOKUP pulls the values from a given backend list.

It is also true that this pleasure is suddenly stopped when you receive an unexpected #N/A error.

Do these things to have peace of mind and confidence in your results…

  1. Place the lookup table in a separate sheet. There are several reasons to do this: 1) keeps your sheets clean and organized, 2) Keeps your table in a central location and consequently updated easily, 3) Avoids deleting columns and rows by accident since they are placed away from the action.
  2. Move the index column you have in mind to the left. VLOOKUP only searches the left index column.
  3. Check that the index column does not contain duplicates.VLOOKUP does not retrieve other than the first occurrence.
  4. If your index column contains texts, check for wrong spelling. Wrong spelling is only detected by humans.
  5. Check that your left column does not contain leading or trailing spaces. Inter spaces are less common but they are equally a threat. Keep in mind that the values you search must be exactly the same on both sides, “Susan” in the frontend won’t match with a “Susan  ” or ”   Susan” in the backend (note space  ”  and the missing “)
  6. Check that the data of the left column is correct. Numbers must not be stored as texts, dates must not be stored as texts, numbers should go with the correct decimal delimiter, etc.
  7. Check that your index column is sorted ascending (this is a sine qua non condition for an approximate match VLOOKUP).

If you want to not be in a frustrating #N/A trap, I recommend you  follow the above guidelines.

You usually hurry to seek the pleasure of having the value in the front of your report. Believe me that this  “finding-things-pleasure”  turns into a “pain-escape-attitude”  in seconds.

What do you feel when an unexpected #N/A stops your momentum?


Sine qua non