VLOOKUP formula is a worksheet referencing formula in excel which uses a target cell reference to search it among a range of cells and then matches the cell reference and returns the data to the column index provided whether it is the actual result or approximate result it depends on the user, this formula is used on the column referencing.
Vlookup Formula in Excel – Table of Contents
Excel VLOOKUP Formula
I would like to say VLOOKUP is the most required formula to clear the interviews. Excel is the universal language that speaks in almost all offices. In order to work productively in excel VLOOKUP is the first ever formula you should know.
The syntax of VLOOKUP Formula
Every VLOOKUP formula in excel consists of syntax i.e. arguments. VLOOKUP is no different from other formulas. Let’s look at the syntax of the Excel VLOOKUP function.
The syntax of the VLOOKUP Formula in Excel consists of 4 arguments.
- Lookup_Value: This is the value and based on this value we are searching for other related information about this lookup value. For example, as I told in the above example Product ID is the lookup value.
- Table_Array: Table array is nothing but the main table where all the information resides. From this table using lookup value, we will extract other information’s.
- Col_Index_Num: This is nothing but in the table array we have many columns, but exactly from which column we want the data. This should be a numerical number of the column number.
- [Range_Lookup]: The value we are searching for is approximate or exact. If the search is exactly we need to supply the argument as FALSE (0), if the search is approximate we need to supply the argument as TRUE (1).
What is VLOOKUP Formula in Excel?
The first question from you is what is VLOOKUP?
In VLOOKUP, “V” stands for vertical, so this formula is called Vertical Lookup. VLOOKUP is very useful in searching for the data we needed to be based on one value. For example, if you two tables and in table 1 you have a product table which includes Product Name, Product ID, Product Category, and Product Price. In one of the cell, you have the only Product ID, based on this ID we can extract all the other information related to this particular Product ID.
If the explanation is not going inside the head, now jump to practical examples of VLOOKUP function in the real-time world.
How to Use VLOOKUP Formula in Excel (with Examples)
Below are some of the examples of VLOOKUP Formula in Excel.
Let’s take a look at the example of VLOOKUP in excel. Assume you have a product table in the excel worksheet.
From the above table for the Product Code KC22, we need the sales price.
Step 1: Open Excel VLOOKUP formula in any of the cells.
Step 2: The first thing is lookup value, here lookup value is our product code i.e. KC22.
Step 3: The second thing is Table Array i.e. where is the actual table range is, in this example our table range is from A1 to C10. But since we have selected the Product ID as the lookup value we need to start our table from the same column only.
Step 4: Now from the table from which column we need the data. In this example, the column number is 2.
I know you have a doubt because Product Price is there in the 3rd column of the data but I am telling column number is 2. The reason behind this is column numbers counts from where the table array starts. In this example, our table starts from B to C, since we have Product Price in C column number is 2, not 3.
Step 5: The Last argument is the range lookup. We need an exact match so select FALSE as the argument.
Note: You can also type zero instead of FALSE.
Step 6: Close the bracket and hit enter key we should get Product Price for the code KC22.
Like this using Excel VLOOKUP function, we can fetch the data. We will see more examples below reading on.
Assume you have employee salary information in the first table and from this table you need the salary information for three employees.
Step 2: Let’s open the Excel VLOOKUP formula in the F2 cell.
Step 3: Lookup value is our employee code, select E2 cell as the reference.
Step 4: Table array is from A2 to C11. Since we have selected Employee Code as the lookup value we need to select the table array from the first column itself, so select from A2 to C11.
Step 5: Column number is the third column, so mention 3 as the argument.
Step 6: We need an exact match, so mention zero as the argument.
The result is shown below:
Step 7: Drag the formula to other cells.
Oh!!!!, hang on we got error values as #N/A.
- The first problem is employee code EMPID015 is not there in the main table, so we got error value as #N/A.
- For the second problem press F2 (edit key) in F4 cell.
As the formula copied down the reference of table array changed. So while giving the reference to the table array we need to make it absolute excel reference by pressing F4 key once. It will insert the dollar symbol for row & column.
Things to Remember About VLOOKUP Formula in Excel
- VLOOKUP can look from left to right only.
- Don’t forget to lock the table array range by pressing F4 key once.
- This is just the introduction part of the Excel VLOOKUP formula. Refer our other articles on VLOOKUP: Fixing VLOOKUP Errors and IFERROR with VLOOKUP,
- If the lookup value not found in the table array we get #N/A
This has been a guide to VLOOKUP Formula in Excel. Here we discuss how to use VLOOKUP Formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –