VLOOKUP is an extremely useful function and is much easier to use than you ever had thought about. So, before you start off, you should know the basics pertaining to the function. Check out the tutorial as you try using VLOOKUP in different versions of Excel and on other applications like the Google Sheets.
What is VLOOKUP?
Usually, VLOOKUP enables you to search information from another excel sheet. For instance, if you have prepared a list of products then you can find the price from another sheet.
In this situation we are going to the find the price ‘Photo Frame’. Once you have learned the syntax of VLOOKUP, you would be able to use in spreadsheet with many columns and rows. Searching for relevant information would then be a breeze.
Here we will add the formula in cell E2. As it’s with every formula, usually, you’ll have to start with the equal to (=) sign followed by the formula name. Later, you have to add the arguments.
How to Add Arguments?
Once you type ‘=VLOOKUP(“ the first argument denotes the name of the item for which you need to perform the search. In this case, it’s Photo Frame. Since the name is nothing but a string you should include it in double quotes.
Next, you should include the cell range from where you need to select the value. In this situation, the data range is A2:B16. Remember to include a comma (,) soon after the first argument. It would look something that’s mentioned below.
=VLOOKUP(“Photo Frame”, A2:B16
NOTE: It should never be forgotten that VLOOKUP allows searching for data from the first column only. In this case, we have considered column A. In certain cases, you need to shuffle the columns so that the first column contains the right kind of data.
The column index number is the third argument in VLOOKUP. Here the index is 2 since it’s the second column in the spreadsheet. So, now the function would look something like as shown below.
=VLOOKUP(“Photo Frame”, A2:B16, 2
Lastly, you need to enter the fourth argument. This has to be TRUE or FALSE. Alternatively, you can type 1 for TRUE and 0 for FALSE. If you enter TRUE, then it would search as per an approximate match. Generally, this can be used only when the table has the first column with numerical values. Since we have to find the exact match, you should mention the value as ‘FALSE’. Finally, you then have to type the closing bracket.
=VLOOKUP(“Photo Frame”, A2:B16, 2, FALSE)
After you hit ‘ENTER’, the formula would return an answer that’s 9.99.
Another Example for VLOOKUP
Let’s say you have included a third column named as ‘Category’. This time, rather than searching for the price we would find the respective category.
In order to find the right category, we have to change the second and third argument. While the range would be changed to A2:C16, you should mention the column index number as 3. The applied formula would then be as follows
=VLOOKUP (“Photo Frame”, A2:C16, 3, FALSE)
Once you press the ‘ENTER’ key, you would fetch the desired value. With respect to the spreadsheet, you would get ‘Kitchen’.
If you’re searching the fields for a long list, then you need to provide the cell range in the first column. Here it’s A2:A16. Later, once you have applied the formula you need to copy paste it across the empty column.
NOTE: Remember to add the dollar sign ($) before the first argument ($A2:A16). In case you forget, then the column range would shift as you move down the sheet.
Hope this article helps you learn the basics of VLOOKUP. Do share the information with your peers and friends.