VLOOKUP is a great tool but if you have used it for your daily reporting purposes you may have notice some shortcomings…
- VLOOKUP 1 searches on a left index column. Why left? Database tables have the key column on the left so I assume they consider this in the design. (INDEX and MATCH allow you to set the index column)
- VLOOKUP 1 does not retrieve the location of the found value in terms or row, column or cell reference. This data is crucial in some applications, for example: when you want to use adjacent data or take decisions about what to do next according to the location of the value. The function MATCH retrieves the row number of the value found
- VLOOKUP 1 only retrieves the first occurrence. This is especially dangerous when you use wildcards or when you have duplicates with the wrong data
- VLOOKUP 1 searches based on single criteria only. Why? Well, there are workarounds: Helper columns, array Formulas and SUMPRODUCT
- VLOOKUP 1 result is affected by an index column not sorted ascending in the lookup array
- VLOOKUP 1 does not allow you to decide which of the nearest values to choose (the lower or the greater one)
Conclusion
You got the point, VLOOKUP 1 is not enough.
The fact is that your reporting purposes go beyond a simple exact/approximate match based on a left index column. You should be able of…
- Searching data based on multiple conditions
- Searching data based on advanced conditions
- And much more…
Say welcome to the formulas that will allow you to overcome these limitations: INDEX and MATCH, VLOOKUP + Helper Column, SUMPRODUCT, Array Formulas.
Popularity: 5% [?]
Post a Comment