Excel – Saturday

training 2

To start of the first “Excel Saturday” I will go over two lookup formulas. They are essentially the same but are used in two different scenarios. One is called a VLOOKUP and the other is called an HLOOKUP. The difference between the two is the first letter of the formula. The H stands for horizontal and the V stands for vertical. If your data set is vertical you would use the VLOOKUP and if your data set is horizontal you would use the HLOOKUP. Don’t worry I’ll have an example shortly. To illustrate here are two data sets.

Data Set

The left is the Vertical and the right is the Horizontal data set. Now let’s say you have the names but they are in a different order then the data set and you want to lookup and fill in the values from the data sets. This is the blank table starting in cell B15.

Using the VLOOKUP formula and the data set in the top left of the formulas will look like this next picture and the end result will be to the right.

vlook result

For this example we will look at the “Amy” line. Here’s how to understand what the formula for the hours in G16 is saying.  This formula starts off by identifying “who” we are looking up. The $B$4:$D$13 is the data set area and the “2” is stating which column of the data set is going to be retrieved. In this case 2 is the hours column. The false means that we want the result of the exact match of the name. The only difference in the dollar column formula is the number 3 which means that the we want the data in the third column for the dollar amount.

The HLOOKUP we will be using the respective formula and using the data set to the right, the horizontal data set. The results and the formula will look like this.

H lookup result

Everything is the same except that in the HLOOKUP the data set area is different because the data is in a different spot and the numbers to retrieve are theoretically the same but instead of telling it which column you are stating which row.

For more information visit www.eagleeyebookkeepingservicesllc.com


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s