How to Build a VLOOKUP Exact Match Formula that doesn’t Fail

Answer yes to these set of questions when you write a VLOOKUP 0 (exact match). This way you will always build Reports in which you trust.

At the Backend (haystack)

  1. Is the lookup index column located at the left?
  2. Is the index column free of duplicates?
  3. Is the index column free of weird characters? For example: ” for inches, m for meters, %, $, extra commas, etc. Use CLEAN, TRIM, Text to Column
  4. Is the list in a lookup sheet? (Recommended)
  5. Is the index column format correct? Date, %, etc. Be careful when you look up numbers, precision displayed is not the number Excel really stores and use in computations
  6. Is the external lookup book referenced? (in case the list is located in an external workbook)

At the Frontend (needle)

  1. Is the format of the front end cell the same of the backend index column? Especially in dates. Be careful about precision displayed when you use numbers
  2. Is the lookup_value free of weird characters? Use CLEAN, TRIM, Text to column
  3. Is the lookup_value referring to a left index_column?
  4. Do you have a #N/A trapping error formula? Use ISERROR, ISNA, IF. Now in Excel 2007 you can use IFERROR (faster). #N/A for VLOOKUP 0 means item not found so you can use a message to show this or take an action
  5. Is the range_lookup argument not omitted? (Omitted means TRUE or approximate match)
  6. Is the table_array argument reference set to absolute? You usually copy formulas down so the table_array may change in undesired ways
  7. Is the lookup_value correctly entered at the cell? For example: “Susan Martines” instead of “Susan Martinez”
  8. Is the column number not hard coded? (optional, when you move, delete, cut columns in the lookup array)

Conclusion

VLOOKUP formulas face two main problems:

  1. Excel users usually don’t invest any time in preparing the backend list to be ready for being pulled
  2. VLOOKUP is usually used for things it was not designed for

For the first point, the checklist of this article will surely help you. For the second point, you need to learn and apply the potent lookup formulas Excel disposes to overcome those limitations. Learn more