Answer yes to these set of questions when you write a VLOOKUP 1 (Approximate 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 external lookup book referenced (in case the list is located in an external workbook)
- 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 index column sorted ascending? (CRITICAL)
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 column number not hard coded? (optional, when you move, delete, cut columns in the lookup array)
- 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 1 means item not found (lookup_value is less than all values in the index column of lookup array) so you can use a message to show this or take an action (for example: use the lowest value)
- Is the range_lookup argument set to 1 or omitted?
- 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?
Well, your VLOOKUP 1 will be ok but this does not mean this formula will attend all your Reporting needs.
It is very limited; see below why you need other Formulas to extract data from Excel Tables intelligently…
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: 4% [?]
Post a Comment