Vlookup in Excel

What is a vlookup function in Excel?  Let’s pretend I work for the budget office.  My boss wants me to compile a list of information about the states and their tax rate, unemployment rate, etc.  Since I’m just an accountant, I don’t have all of this information on my hands. I have to obtain data from many sources. One person sends me a file on unemployment rate.  Another one sends me information on the sales tax rate. My job is to consolidate the data.

Here are two sample files.

Figure A – list of unemployment rate (Book1)

Figure B – List of sales tax rate (Book2)

So how do I consolidate file A with file B. To do that, I create a list of 50 states in a blank worksheet. List Alabama to Wyoming in order in a new worksheet.

You should have something like this:

Figure 3

In cell B2 of figure 3, write this formula =VLOOKUP(A2,[Book1]Sheet1!$B$2:$C$52,2,FALSE).

I will now dissect the formula.  In Excel, a formula always start out with the “=” sign.

Vlookup is a name of a function. V is for “vertical”. So, you’re telling Excel, with this formula, to “lookup” a value in a a set of fields in vertical direction. Does that make sense?

I will now analyze the arguments that go inside the parentheses ().

All formulas have criteria and parameters.  Even the SUM() function has criteria.  You’re adding what values? Values are criteria.

There are 4 arguments (criteria) in a vlookup function.

  1. The first one is the “lookup value”.  In our case, it is the state name (i.e. Alabama, etc). If you look at figure 3, Alabama is in cell A2.  That is why I wrote =vlookup(A2,…).  Now, you can replace A2 with “Alabama” but I don’t recommend you doing so.  You want your formula to be versatile and copy-able.
  2. The second argument that comes after the “,” is called “Table_Array”.  A table array is a list.  In our example, we have two lists for unemployment and sales tax rates.  In cell B2, we are trying to get the unemployment rate of Alabama, so our “table_array” is the unemployment rate list.  A word of advice, your table array should begin with the same value as your “lookup” value.  I am looking up states.  My table_array should begin with the names of states. You can get this table_array by going to the worksheet where your list of unemployment rate resides. In my case, it resides in a Book1, in Sheet1, and from cells $B$2 to $C$52. Make sure you have dollar signs around the cells.  Those signs make the cells absolute so that when you drag down the formula for the other 49 states, the table_array remains static. Try it with and without the signs and see what happens.
  3. The third argument is the “Column_index_num” . It is the column number in the table_array from which you want to the matching values to be returned.  The unemployment rate is in column 2, so I typed “2”.
  4. Always write “FALSE”. Trust me on this.
Finally, once you got the answer you need, drag the formula down to the other 49 states to get their rates.  Follow the same steps to get sales tax rates.
If you have questions, write them in the comment box below.
This entry was posted in Excel. Bookmark the permalink.

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