Formula Friday #3
Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
This week we are discussing the most popular Function in Excel – the VLOOKUP
My introduction to a VLOOKUP was someone showing me where I needed to click, what I needed to select and when I needed to close the brackets. It was until much much later – when I had to show someone how to do a VLOOKUP – that I started to look at what the VLOOKUP actually does.
The first place I went to was the Insert Function option in Excel. I figured the people who created the Function would be best placed to explain what it actually does.
In summary, a VLOOKUP returns the corresponding value, from a specified column, that is in the same row as your reference cell/lookup value.
Here we want to return the ranging of the line number in cell E2. To begin the formula we select cell E2 as the lookup value (reference cell). The table we want to search is columns A to C. The value we want to return is in the third column of that table. And we want to find the exact match in the first column (column A) so we end the formula with a 0.
If the data was in ascending order we could use 1. But I would always recommend ending with a 0, that way you will always get the exact match.
The important thing to remember is
Whatever you want to reference needs to be in the leftmost column. In the above example the line numbers are all in column A, but if the line numbers were in column B the formula would look like this.
Our reference cell stays the same, E2, but the table we want to reference is now columns B to C. As column B is now the leftmost column that includes a match to the lookup value in our reference cell. Column C is also now the second column of the table and we still want to find an exact match.
Once you get your head around what a VLOOKUP does and why; you can start making it a little more fancy.
Let’s take it up a notch
You can directly type your lookup value into your VLOOKUP formula like this.
Also note I was more specific with the reference table selection this time. Only selecting A6 to B653, and not the entire column. As that was the range of data that had the needed information.
The VLOOKUP Function also works well combined with other Functions.
Our previous two formula Friday posts have used the TEXT Function, so I thought it only appropriate to show an example of the TEXT Function and VLOOKUP Function working well together.
If this is your first time seeing the TEXT Function in action. I have a post which explains more about what it does and how to use it, which you can read at your leisure.
On with the blog!
In this example we are converting the data in the reference cell to text, ensuring the returned value is 7 characters long
Our reference table in this instance is on a different tab in the same workbook.
The value we want to return is in the third column. As always, we are looking for an exact match.
That example was nice and straight forward. All the data we want to return would go into nice neat columns. So if we needed to make any changes to the order of the data, or the columns the values need to be returned from, it would be relatively quick to amend.
But what if we have an irregular format to use
Let’s say you need to create product labels for sign off, and the layout hasn’t been 100% set. You have an idea of what needs to be included on the labels, so you can get started. But want to make sure the formulas are setup to be flexible enough for any changes. If that sounds familiar read on!
So far we have been typing in the number of the column we want to return the value of. But you can also reference a specific cell with a number in it, see below:
In this example we our lookup value is the Line number in cell C4
The table we are referencing is on Sheet1, columns A to H.
The difference is, we are now using cell B1 in the sheet we are working in, Sheet2, to refer to the column we want to return the data from.
Remembering to ensure the exact match.
The same logic has been applied to each of the results in the table, excluding the line number. That way, if the order of attributes changes. Let’s say price needs to be where category ID is. All that needs to change is the number in the corresponding cell in row 1. Rather than going into each formula and changing each of the column reference numbers.
To change the names over, for price and category ID. A well placed ctrl+H will sort it out.
How did I do?
I’m pretty sure that was longer than a five minute read. But a VLOOKUP is such an important Function in your day to day I wanted to spend a bit more time on it.
If you feel as if you are in need a bit more info. I have a post that discusses VLOOKUPs, HLOOKUPs and my current favourite lookup method, INDEX MATCH. It’s a longer read so make sure you’ve got a tea or coffee with you.
Don’t forget if you have any burning Excel questions you can always drop me a line.
Until next time