Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
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.
For example;
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.
=VLOOKUP(E2,A:C,3,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.
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.
=VLOOKUP(E2,B:C,2,0)
Once you get your head around what a VLOOKUP does and why; you can start making it a little more fancy.
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.
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
=VLOOKUP(TEXT($F3,“0000000”),
Our reference table in this instance is on a different tab in the same workbook.
=VLOOKUP(TEXT($F3,“0000000”),‘PRD INFO’!$K:$M,
The value we want to return is in the third column. As always, we are looking for an exact match.
=VLOOKUP(TEXT($F3,“0000000”),‘PRD INFO’!$K:$M,3,0)
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.
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
=VLOOKUP($C4,
The table we are referencing is on Sheet1, columns A to H.
=VLOOKUP($C4,Sheet1!$A:$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.
=VLOOKUP($C4,Sheet1!$A:$H,Sheet2!$B$1,
Remembering to ensure the exact match.
=VLOOKUP($C4,Sheet1!$A:$H,Sheet2!$B$1,0)
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.
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
Ax
Finding an exact match for your needs on the internet can, at times, feel like trying to find the missing pair to your odd sock. You know it’s in the drawer somewhere, but you just can’t see it for love nor money. That’s where I can help, think of me as your Excel Agony Aunt, send in your lonely heart letters and I will find your perfect formula partner.
I can help bring your ideas to life. Let’s talk about what we can build together.
How to find or remove duplicates Formula Friday #5 Formula...
Read MoreHow to use nested IFs Formula Friday #14 Formula Fridays...
Read More