VLOOKUP is a function that helps you to find the thing from a table array, the data you stored on the spreadsheet. There are four parameters in the VLOOKUP function. Let us start with the steps of How to use Vlookup in MS Excel 2016?
Steps to use Vlookup in MS Excel 2016:
The data I have, contains the number of copies of an XYZ book sold in each month from the year 2011 to 2015.
Step 1: First, confirm that the data from which you want to look up exist on your spreadsheet. Then select a cell to write VLOOKUp formula and write “=VLOOKUP(“, you will find it there in a drop down list. Once you open a bracket of the VLOOKUP function what parameters are required, will start appearing beside your cell.
Now I want to search, how many copies of the book is sold in the month July of the year 2014.
Step 2: The first parameter that you require is a lookup value, Lookup value is a value of which you are searching the related data.
In the image below you can see as I selected the cell July as I want to search for the sale of the book in the month July. You can also write the lookup value in cell manually.
Step 3: Next is the time to select the range from where you want to search the data. Remember that the lookup value that you selected in the above step must be the first column of your selection range i.e. my lookup value is in cell B10, so B must be the first column of my selection range. You must also remember that the column holding your result must be there in your selection range.
Step 4: Next you have to select the column number from the range you selected in above step which contains your result i.e. the column which will return the result. The first column of the selected range is column no.1. For example, The range I selected is B3:G15, so, column B is column no. 1, column C is 2 and so on.
As I want the Sale of Book in month July from the year 2014, and year 2014 appear in the 5th column of my selection range.
Step 5: The last parameter is the lookup range. i.e. TRUE will return the approximate match of the search and FALSE will return the exact match of the search. I chose FALSE to get the exact result.
Step 6: You can confirm that the sale of the book in the month July of the year 2014 is 735.
Step 7: Instead of typing the formula manually you can get the dialog box to let you help for selecting the parameters. For that press Ctrl+A after writing =VLOOKUP( in the cell.
Fill all the parameters and click OK.
Let us see How the approximate matches are found. I have the data containing the range of percentage and the grades equivalent to them. I want to find the grade equivalent to 76%.Step 1: In the VLOOKUP formula, my first parameter i.e. lookup value will be 76 as I am searching grade for percent 76. My range will be the column containing percent and their equivalent grade. My column number that will return the Grade is 2. Now that I want the approximate result, I chose lookup range TRUE.
Step 2: You can observe that the candidate scoring below 77 will have C grade.
Leave a Reply