Actually in Microsoft Excel we have these 2 function index and match when combined it become very powerful formula even better than vlookup function. To understand this formula may be difficult at the first place but when we use to it sooner or later it become normal. This tutorial actually for me to remember how it work but if you feel useful too then much better. When I try to use vlookup function but fail because data position at reverse side, that why I’m trying to look for other alternative. So, I found this function when Google for solution.
To make the scenario more simple and easy to understand. Let try example below:
This quantity keep on changing. What we want here is, the Maximum fruit that having the highest quantity. Forget about Visual Basic for Application just use formula. Let say we want the result in F3. Try this formula and insert into Cell F3:
The explanation refer below picture.
This definition I get from help for Index function the equation as below:
Array is a range of cells or an array constant.
Row_num selects the row in array from which to return a value. If row_num is omitted, column_num is required.
Column_num selects the column in array from which to return a value. If column_num is omitted, row_num is required.
and for Match equation as below:
Lookup_value is the value you use to find the value you want in a table.
Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.
Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.