How to Use V-Lookup Formula In Excel
Hello in this article we will show you how to use the V-lookup feature in Excel.
Excel V-lookup allows you to look at a particular value from a large set of records, and the best thing is that you can use this return value on additional formulas or use it as a reference on another worksheet!
In this example, let’s pretend that a company is moving to a new building, and we were asked to create a report that shows each employee’s department, and their location ID in the new building. We will build this report together.
We have missing information from two sources and V-lookup will help us bring them together.
Let’s get started and create the first report. In the raw data worksheet, we have the list of employees that were sent with unorganized and scattered data, we exported this worksheet from another system. We are missing their department and new location, we just have their supervisor’s name.
Now in another worksheet we just have the departments, supervisor, and location ID, we called it Locations worksheet.
The only link between the two worksheets is the supervisor’s name.
This is a very important note about V-lookup, notice that it looks for the information from left to right, so if the value or column that you want in return is on the left of the table like this example,
Then the Vlookup will give you the N/A error that you see here
In this example let me show you an easy way to memorize how V-lookups work. Do you remember the yellow pages back in the days?
Well! I remember looking for everything we needed and the yellow pages, at least for me was my Google back then, and each page had a column with the name, the next column had the address, then the number, and so on, as you see in this example.
Think of the V-lookup formula like this:
1.) Who are we calling from the yellow pages?
2.) This is our lookup value. Where are the Yellow Pages?
3.) This is our table array, in which column is the value I need from the YP? That’s our column index and lastly, false for an exact match and true for an approximate match. 90% of the time in Excel V-lookup, at least in the examples from our students, you are using the False match
So in this cell we will write =Vlookup( and select who we are trying to call, the supervisor in this case, type a comma to make room for our next step.
We are going to click on the locations worksheet because here we can identify each department by its supervisor. After the comma we are going to select the whole Yellow pages because we don’t know where the departments are in the book, so we select everything followed by another comma. In this case the yellow pages were in my locations worksheet because there I can see the department by supervisors.
Now the only thing we know is that the information or the value that we are looking for is somewhere in this column, and we need to tell Excel the column number so it knows where to look for it in the Yellow pages. In this case, after we select the yellow pages, we will enter column number 3 but we are not done with the formula, we need 1 more step.
Since we are using this formula in the rest of the columns we are going to lock it by pressing F4, so my Yellow page selection does not change and stays put even when I drag the formula across the rows. If you miss this important step you will see N/A errors across your data.
In the last argument, we are going to select False and press enter because we want an exact match, and by the way False in many examples and cases is the most used criteria when it comes to V-lookups. We are ready to find these values so let’s go ahead and close parenthesis, click enter and voila!
You are going to see the department names based on the supervisor.
Let’s repeat the same steps for the location, we’ll type in cell F2 =Vlookup we can call the supervisor again since it is linked to the location ID, I picked the supervisor again followed by a comma. Now we go again to the location worksheet, select the entire range, click anywhere on my YP selection and press F4 inside the formula to lock it.
Let’s enter the column number where the location ID is in this case is column 3, comma, and false for an exact match, we are going to close parenthesis, press enter and the location ID shows up.
So the steps for Excel vlookup are: =vlookup, open parenthesis, first select who you are calling, then highlight the entire YP, lock it using F4, enter the column number and false, close parenthesis and press enter. So it looks like the report I showed you in the beginning, since this is already formatted as a table, click anywhere inside the table, go to the design tab and select a design of your choice. I also checked banded rows so the report is easier to read in this example.
Now let’s go over an example where we use an approximate or true match at the end of the vlookup instead of false as we showed you previously. We have a list of salesmen and we need to calculate their commission based on a chart next to it. If the sales are 50000 or less the percentage is 11%, if it is greater than 50 but less than 80000 then it goes up to 12% and so on. The great news is that the true match works with the closest or approximate match from small to large, so it will calculate the commission easily for us, let’s do it.
Before we start, let’s convert the commission chart into a table by clicking anywhere inside the chart, press CTRL +T, check my table has headers and press ok.
You are going to notice that by converting it into a table I no longer need to press F4 when I highlight my YP.
In this case the commission chart In the cell D2 let’s type =vlookup(sale C2 or 154000, this time the yellow pages are inside a table so I don’t need to lock it, another comma, type column number 2 for the commissions, true match close parenthesis and press enter.
So let’s verify the results, since the sales for David Bradley were 154k, it is greater than 150 but less than 235k so the commission is 13% and this is correct
To finalize this example let’s double click on the lower right-hand corner of the cell
so it auto-fills all the way until the end of the column.
For the total column just multiply the sales by the commission, by clicking equal C2*D2 divided by 100, and press enter.
Click on the lower right hand corner to auto-fill, let’s enter some dollar signs and that’s it!
We hope you enjoyed this tutorial, don’t forget to subscribe and like what other features in Excel are in our YouTube Channel
Would you like to learn about it?
vlookup in excel formula
vlookup formula pdf
vlookup example between two sheets
how to use vlookup in google sheets
how to use vlookup in excel 2020