Lets start with a list of people and their type of unit. We also have a list of types of units and their associated rates. How do we get the rate information into a new column next to each person’s name without typing it by hand? Excel can do it!
First, we will use a MATCH function, which searches for a value in a list and will return where it is in that list.
=MATCH(value to search for, list to search in)
Putting the formula in D2, you can see we put in the type of unit, which is the value we are looking for, and then we entered the range where you can find that unit. Don’t forget to put the dollar signs into the range so that when you drag the formula down the range doesn’t update!
But the number in that box is the row number that we want, but it doesn’t actually tell us the rate. To do that, we will need to wrap the MATCH function in another function, an INDEX function.
=INDEX(array that the cell you want to return is in, row number of cell, column number of cell)
In this case, we will select the entire type/rate table as the first part of our formula, the MATCH formula we just made will go inside the “row number” part of the formula, and then our column number will be 2.
Don’t forget to put the dollars signs in the array inside the INDEX function, too!