MATCH is a handy function that you can use to find the row number for a value.
Our exercises and the results are in the Match file.
Let's go back to the maximum exercise we did a couple days when we used an array to find the row number of the maximum value. While we did it with an array and the maximum function for the educational value, in this case we could do it simpler with the match function.
Here is what we had before: {=MIN(IF(orders=MAX(orders),ROW(orders), ""))}
The Match function can do the same in an easier to use formula, as we see below:
The formula is =MATCH(E3,orders,0). The parts are:
What value is to be looked up- in this case, the max number of orders
What range are we going to look in- in this case, the named range called "orders".
The third value needs some explaining- we input 0 for an exact match.
The options for the third value are:
0- exact match. The advantage of this is that the range does not have to be sorted in any order.
1- the largest number < than or = to the lookup number. In this case, the range must be sorted in ascending order.
-1- the smallest number > than or = to the lookup number. In this case, the range must be sorted in descending order.
Generally we prefer 0, to avoid the sorting issue.
Note we could have modified the Match formula to =MATCH(Max(orders),orders,0) and also got the same result.
Recall in yesterday's lesson that the Index function returns a value from a reference. Put this together with the Match function and you can have yourself a different way to do a lookup. Using this, we can modify the Rep Number calculation in our example.
The formula is =INDEX(rep,MATCH(E3,orders,0)). The parts are, working from inside out:
MATCH(E3,orders,0)- We use the Match function like we did before to find the row number corresponding to the Max number of orders
=INDEX(rep,MATCH(E3,orders,0))- Having the row number in place, we then can use Index to return the value, in this case the rep number, that corresponds to that row.
Note by having this combined formula, you cut out the need for the second calculation for the row number. Your worksheet can look sharper without an intermediate calculation.
We can go further with this. We can create a lookup to pull up the orders for any rep we input in a lookup cell, by again using both Index and Match.
First, the intermediate way, where we do a separate row calculation first: =MATCH(I3,rep,0).
The formula for the total orders for this rep is: =INDEX(orders,I4). We use Index to return the value for the row number calculated in the cell above by the Match function.
However, let's also show the streamlined way, without showing the separate row number calculation, so you can look really sharp!
The formula is =INDEX(orders,MATCH(I8,rep,0)). We basically combined the two separate formulas into one.
Note- this is a good formula building practice. Build the individual pieces in separate cells, so you can make sure each individual formula works. Then combine the two formulas into one.
With Excel, there can be multiple ways to skin a cat. We could have used a vertical lookup function and achieved the same result:
The vertical lookup version of the formula is =VLOOKUP(I12,rep_orders,2,FALSE). (Note we created a named range "rep_orders" that covered both the rep name and number of orders.
With Match, you have another tool in your Excel tool chest, whether you use it standalone or in combination with Index to do a lookup.