Here’s how you can produce high-quality VLOOKUP formulas:
Of course, I am assuming that your backend list is clean and ready to be pulled.
What does this mean?
- Your index column is free of duplicates, leading and trailing spaces, wrong spelling, etc.
- Your index column is the leftmost one of your backend list.
- The format of the left column is correct: numbers not stored as texts, dates stored as serial number dates.
- The index field is sorted ascending for approximate match VLOOKUP.
Once you have your backend list ready, then you can apply one of these techniques to create more professional VLOOKUP formulas…
1) Limit the entry possibilities in the lookup cell
Many times your NA error is a result of wrong spelling. For example, you type “Susan W” but your index column contains “Susan Wilson”.
Limit the user entry possibilities with a Data validation list. You can do it using the Excel Data Validation command.
Once you trigger this command go to… Settings-Allow-List-Source
Choose the source as the entire index column. Each entry will be shown in the lookup cell as a list you can pick a value from. For example: “Susan Wilson”, “Thomas Grady”…
2) Increase the sense of trust in results by showing a message instead of the NA error
Increase the sense of trust in results by showing messages with meaning, for example: “item not found”, “missing entry”, etc.
Apply one of these solutions:
- Embed your formula into an IF function. For example: =IF(ISNA(VLOOKUP(),”Value not found”,VLOOKUP()).
- Use the IFERROR function (Excel 2007). For example =IFERROR(VLOOKUP(),”Value not found”). This solution is faster because Excel evaluates VLOOKUP once.
3) Avoid a VLOOKUP crash due to a deleted or moved backend-column
Use a formula to reference the col_index_num argument. If you move, delete, cut columns in the lookup array; avoid an easy VLOOKUP crash by doing this…
- Don’t hard code the column number.
- Create a row above the field headings in the backend, then number each column with the formula =COLUMN()-number of columns to the left.
- Now you can reference the col_index_num argument to the corresponding cell that contains the column number.
If someone inserts a new column in the table, the VLOOKUP still links to the correct column, and if someone inadvertently deletes it, then you will immediately know about it as your VLOOKUP won’t work anymore (#REF!).
Conclusion
VLOOKUP is easy to implement but difficult to rely on. Moreover, it is not easy to detect the areas to improve once you have grasped the fundamentals. The above tips help you to improve your formulas and take them ahead.