Exploring this Excel Function
At the start of a Microsoft Excel course I always ask people – “What do you Need to Know”. I’m trying to find out what each attendee wants to achieve from their time spent with me.
Often the VLOOKUP function is mentioned. “I’ve heard other people talk about it, but I’m not really sure what it does”.
You won’t be surprised to learn that it is designed to look things up! However, there are two distinctly different ways that it can be used.
VLOOKUP – Using a “Banding Structure”
Have a look at the following table of discount rates:
Down the left hand column of this table there are a set of Invoice Amounts. The next three columns show what discount a customer would be allowed (depending on which Rate they qualified for).
Any customer who’s invoice amount is between zero and £999.99 would get 3.0% if they are on Rate1; 5:0% for Rate2 or 7.0% on Rate3.
If the amount is between £1000.00 and £1999.99 the values are 5.0%; 7.0% and 10.0% respectively.
The table sets up a “Banding Structure” so that three discount rate percentages can be found for any invoice amount.
Let’s have a look at a VLOOKUP function that would achieve that for us:
First of all – we can read the description of what a VLOOKUP does…
“Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in ascending order“
The first bit of that is important. The invoice amounts have to be in the first column of your table. I had a customer recently who couldn’t get VLOOKUP to work. After a bit of investigation, I found that he had put his list of “invoice amounts” in the third column. That just won’t work.
Function Arguments is just a posh way of saying “the things you have to fill in to get the function to work”. In this case I’ve used:
Lookup_value – C9 – That is simply the cell where the Invoice amount is typed – (looks like I used 1750 as the value)
Table_array – A3:D7 – Where the look-up table is (no need to include the row which has the headings)
Col_index_num – 2 – used to look up the correct Rate (in this case Rate 1)
The first two Arguments shouldn’t cause you too much trouble. It is the third one that can confuse.
Col_index_num is the column number in table_array from which the matching value should be returned.
The first column of values in the table is column 1
People are used to referring to columns in Excel by a letter – the values for Rate 1 are in column B of the worksheet. So why are we asked to enter 2? The point is that Excel wants to know that Rate 1 is in the second column of our table. It would make no difference if the table started at cell ZZ1000, the figure for Rate 1 is still in the second column; the correct Col_index_num would still be 2.
If you want to type this directly into the worksheet, put =VLOOKUP(C9,A3:D7,2)
As Rate 2 is in the third column of our table, the correct function to look it up would be =VLOOKUP(C9,A3:D7,3)
Use =VLOOKUP(C9,A3:D7,4) to look up Rate 3 – because it is in the fourth column of our table.
VLOOKUP – Finding an Exact Value
A totally different use of the function is to enter (say) a Part Number into one worksheet and use VLOOKUP to find a matching entry in another worksheet. Often this is used to extract more information (perhaps Description and Price) from the second sheet.
Here is an example of that:
… and the function to extract the Price:
Looking at the Arguments:
Lookup_value – H2 – This is where I typed in the Part Number to look up – (S1537)
Table_array – Sheet2!A2:C5 – (looks like the table is on Sheet2)
Col_index_num – 3 – (the price is in column 3)
Range_lookup – FALSE – (this is new!)
So what is Range_lookup all about?
Range_lookup is a logical value: to find the closest match in the first column (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE
In our first example, the Invoice Amounts are in ascending order. It is possible to type in a value of 1750 and get an answer. Excel looks down the first column of the table and when it gets to a number higher than 1750, it gives the percentage from the previous row. This can only work if the amounts are in ascending order. That is why I left Range_lookup blank (I could have entered TRUE).
However, in the second example, the Part Numbers are not in alphabetical order. I need to put FALSE for the Range_lookup to make Excel look for an exact match – otherwise the function won’t give the correct answers. This does mean that if you enter a Part Number which isn’t in the list, Excel will return #N/A.
VLOOKUP – What does the “V” stand for?
So far the tables have been constructed so the Excel has to look vertically down the first column. That why I’ve used VLOOKUP.
The authors of this next example want to use the CC of a car to look up the corresponding Rates for calculating Expenses. They had a different way of seeing things:
Excel has to look horizontally along the first row find the relevant CC. So an HLOOKUP is needed:
Notice that we are asked to supply a Row_index_num this time. The “Rate over 500 Miles” is in the second row of our table, so an entry of 2 is needed to extract it. Once again, it doesn’t matter that these values are in row 15 of the Excel worksheet – they are in row two of our table.
Want to know more?
In this blog, I’ve explored different ways of of using the look-up functions in Microsoft Excel to extract information from tables. Click here to read about some of the other areas we discuss in our Advanced Microsoft Excel course – or contact me via email@example.com