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)
- Is the lookup index column located at the left?
- Is the index column free of duplicates?
- Is the index column free of weird characters? For example: ” for inches, m for meters, %, $, extra commas, etc. Use CLEAN, TRIM, Text to Column
- Is the list in a lookup sheet? (Recommended)
- 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
- Is the external lookup book referenced? (in case the list is located in an external workbook)
At the Frontend (needle)
- 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
- Is the lookup_value free of weird characters? Use CLEAN, TRIM, Text to column
- Is the lookup_value referring to a left index_column?
- 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
- Is the range_lookup argument not omitted? (Omitted means TRUE or approximate match)
- Is the table_array argument reference set to absolute? You usually copy formulas down so the table_array may change in undesired ways
- Is the lookup_value correctly entered at the cell? For example: “Susan Martines” instead of “Susan Martinez”
- 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:
- Excel users usually don’t invest any time in preparing the backend list to be ready for being pulled
- 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…
Popularity: 6% [?]
{ 2 } Comments
when refering to the INDEX column are you talking about the Col_index_num, I am trying use TRIM but not sure if this is used in the information to be returned or the Lookup_value.
Great information site thanks, I’m wasting so much time trying to work out #NA and how to get rid of it.
Hopefully starting with TRIM at Backend should get me started in the right direction .
Thankyou.
The index column is the leftmost column of your lookup array. Where the Vlookup function searches the lookup_value.
On a table with the fields: Product Name, Price, Sales
The Index column is the Product Name column
For more tips about how to get rid of #N/A, download my report: The Secret Manifesto of #N/As-Free Vookups
Post a Comment