The VLOOKUP can also be used to compare data from 2 different spreadsheets and display the differences between the 2.

With the latest versions of Excel (2010/2013/2016), you will find that VLOOKUPS with even better with the new table feature.  Tables can make your Vertical data dynamic (grows with your data as you add or take away), therefore when you feed a lookup off the table, your lookup becomes dynamic too.

There are 2 types of Lookups available:

• VLOOKUP
• HLOOKUP

What is the difference between a V or a H Lookup?

The V Stands for Vertical and the H stands for Horizontal.  It’s referring to the table you are trying to extract the information from, is it a vertical or horizontal table!

For example:

Let’s say you have a simple list of 1000 customers with 7 columns: First name, last name, Address 1, Address 2, Address 3, County, phone number, and Email. If you want to find the phone number for Joe Bloggs, you can look at every name in that column till you find it. To speed things up, you could try sorting the names alphabetically, but if you have many customers with last names starting with “B”, you might still get a headache browsing the list. Using the LOOKUP function, however, you can simply type in the name and the spread sheet will spit out Joe Bloggs phone number.

LET’S GET STARTED…………

So what’s the difference between a VLOOKUP and a HLOOKUP?

One simply looks up information in Vertical format, the other in Horizontal format.

How can you tell the difference?

Vertical Data

The headings on the list are going across the top

Horizontal Data

The headings on the list are going down the left-hand side

When it then comes to the retrieving of the data from a VLOOKUP or a HLOOKUP, the only other difference is;

The VLOOKUP requires you to put in a column of data you want extracted, whereas the HLOOKUP works off a row.

Using the VLOOKUP function

The VLOOKUP function is made up of 4 parameters:

=VLOOKUP(Cell reference, Table/List, Column, True/False)

For example:

We have an invoice that will automatically enter the description and the price of our products when the product code is entered.

In order for this to work we need to use the VLOOKUP statement, combined with a table named tblProducts.  In the example below, we have named the table tblProducts.  Here we will ask it to look up the product code in the products table and it will come back with a description and a Price for each.

When your building you next VLOOKUP, the 4 parts to the function can be broken down as follows:

1. What unique identifier are you looking up in the table?
2. Where is the table or what name have you given it?
3. Which piece of information from the table, associated with the unique identifier, do you wish to have retrieved for you (column number)?
4. What type of match are you looking for?

The VLOOKUP Broken down

This will search the leftmost column of the table called tblProducts (or grid of product codes/descriptions on sheet 1) for an entry called “PGB777/234”. If no such entry is found it will display #NA in the cell otherwise it will display the match in the 3rd column in the range.

We will look at how to deal with #NA’s with a formula (IFERROR) in later blogs.

NOTE: It is prebuilt into every VLOOKUP, to find the match in the first column of the Table/List.  The VLOOKUP is referred to as the Left Lookup.  It can only work from left to right.

You can overcome this limitation by using INDEX and MATCH instead of VLOOKUP.

The match type is set to FALSE – this tells the function only to find exact matches in the table.

What is the difference between a True or False match type?

• True – Approximate Match

• False – Exact Match

NOTE:  Instead of using the words true or false, you can also use 1 or 0, Binary. 1 for True and 0 for False.

To force VLOOKUP to find an exact match, make sure to set the 4th argument (range_lookup) to FALSE or zero.

To use VLOOKUP in approximate match mode, either omit the 4th argument (range_lookup) or supply it as TRUE or 1.  If you are using approximate mode matching, your data must be sorted ascending order by lookup value. Otherwise, you may get an incorrect results.

Caution: VLOOKUP uses approximate match by default

A few Rules to remember when your creating your next VLOOKUP:

• The left column must contain the values being referenced.

• You can’t have duplicate values in the leftmost column of the lookup range. Once it finds a match, it does not check for others.

• VLOOKUP can only handle a single criteria.

• You can overcome this limitation by using INDEX and MATCH instead of VLOOKUP.

• When working out the Column number, we don’t use the column heading to reference the column, we use a number instead. (Not Column A/B/C but Column 1/2/3 etc).

• For approximate matches, data must be sorted.

• VLOOKUP allows wildcards for partial matching.

• You can use VLOOKUP to replace nested IF statements.

Using the Hlookup() function

The Vlookup looks for data that is stored Vertically (in columns) whereas the Hlookup looks for data that is stored Horizontally (in rows).

=HLOOKUP( Cell reference, Table/List, Row, True/False)

Take a look at the following example:

Here we can see the first row of date contains our customers’ names, they are stored horizontally, this means, if we are doing a lookup we need to do a HLOOKUP.

If we were to set up a worksheet as below, we could make it so that when we type a customer’s name into cell B9, that customer’s Employee No and PRSI number appears automatically in cells D9 and D10.

D9 and D10 need to hold HLOOKUP formulae.