VLOOKUP is a great tool, but if you have used it for your daily reporting purposes you may have notice some shortcomings…
- VLOOKUP 0 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 0 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 make 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 0 only retrieves the first occurrence. This is especially dangerous when you use wildcards or when you have duplicates with wrong attributes.
- VLOOKUP 0 searches based on single criteria only. Well, there are workarounds to overcome this: Helper columns, Array Formulas and SUMPRODUCT.
You got the point, VLOOKUP 0 is not enough.
The fact is that your reporting purposes go beyond a simple exact match based on a left index column. You should be capable of…
- Searching data based on multiple conditions
- Searching data based on advanced conditions
- And much more…
Say welcome to the lookup formulas that will allow you to overcome these limitations: INDEX and MATCH, VLOOKUP + Helper Column, SUMPRODUCT, Array Formulas.