Excel: Filling in information

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!

allinone ratesandpeople.PNG

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)

match.PNG

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.

INDEX

Don’t forget to put the dollars signs in the array inside the INDEX function, too!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

search previous next tag category expand menu location phone mail time cart zoom edit close