Vlookup Excel

Excel VLOOKUP Tutorial for Beginners: Step-by-Step Examples

Excel comes equipped with a built-in function called Vlookup, which stands for the word “Vertical.” This function makes it possible to establish a connection between two or more columns in Excel. To put it another way, it enables you to locate (look up) a value in one column of data and returns the value that corresponds to it in another column.
When you need to find some information in a large data-spreadsheet, or when you need to search for the same kind of information throughout the spreadsheet, use the Vlookup function instead of searching each cell individually.

Usage of VLOOKUP:

Table of Salaries for the Company, which is maintained by the Company’s Financial Team – Company Salary Table is a game in which you begin with a piece of information that is already common knowledge (or easily retrieved). Data that can be used to index other information.

As such, let’s take the following as an illustration: you start with the information that is already available:

(Specifically, the Name of the Employee)

Excel VLOOKUP Tutorial
To discover the information that you are missing:

(In this particular instance, we need to look up information regarding Employee’s Salary.)

Excel VLOOKUP Tutorial

Spreadsheet created in Excel for the instance described above:
Regarding the aforementioned situation, there is an Excel Spreadsheet.
Download the Excel file located above.

In the spreadsheet that was just shown, in order to determine the Employee Salary, which we do not know –

How to use VLOOKUP function in Excel

We are going to enter the Employee Code that has been made available to us.
Excel VLOOKUP Tutorial
In addition, if you apply the VLOOKUP function, the value (the employee’s salary) of the code that corresponds to the employee will be displayed.

Excel VLOOKUP Tutorial
Excel’s VLOOKUP function explained and how to use it
The instructions for utilising the VLOOKUP function in Excel are broken down into the following steps:

Step 1: Go to the cell that you want to view by navigating to it.
It is necessary for us to find the cell in which you want to view the particular Employee’s Salary in order to proceed.
– (Click the cell that has the index ‘H3’ in this specific instance)

Step 2 of the Excel VLOOKUP Tutorial Enter the VLOOKUP function, which is equal to VLOOKUP ()
To use the VLOOKUP function, enter the following into the cell above it: After the equal sign, which indicates that a function is being entered, the ‘VLOOKUP’ keyword is used to depict the VLOOKUP function. This is done by typing =VLOOKUP after the equal sign ()

Excel VLOOKUP Tutorial
The set of arguments will be contained within the parenthesis (Arguments are the piece of data that function needs in order to execute).

The VLOOKUP function requires the following four arguments or pieces of data:

Step 3: If you want to look something up or search for something, enter the lookup value in the first argument.
The reference to the cell that will be used as the “placeholder” for the value that needs to be searched for or the value that will be looked up is the first argument. The term “lookup value” refers to the data that is already available or the data that you are already familiar with. (Since Employee Code is being used as the lookup value in this scenario, the first argument will be H2. This indicates that the value that needs to be looked up or searched will be located on the referenced cell H2.)

Tutorial on the VLOOKUP Function in Excel Step 4: The Table Array
It is referring to the block of values that should be searched for. This group of values is referred to as the table array or the lookup table when working in Excel. In this particular instance, the lookup table would span from the cell references B2 all the way up to E25, representing the entire block that would be searched for the corresponding value.

NOTE: The lookup values or the data that you already know must be placed in the left-hand column of your lookup table, also known as your cell range.

VLOOKUP for Approximate Matches (TRUE Keyword as the last parameter)

Excel VLOOKUP Tutorial Step 5) The column index number is the third argument in the VLOOKUP syntax.
It is referring to the reference in the column. In other words, it informs VLOOKUP of the location where you anticipate discovering the data that you wish to view. (The column reference refers to the column index in the lookup table of the column that should contain the corresponding value.) According to the lookup table, the column labelled “Employee’s Salary” has an index value of 4, so the column reference for this instance is the number 4.

Excel VLOOKUP Tutorial Step 6) Choose between an exact match and an approximate match for the fourth argument.
The lookup for a range is the very last argument. It communicates to the VLOOKUP function whether we desire an approximate match to the lookup value or an exact match to that value. In this instance, we are looking for an exact match, which would be the keyword “FALSE.”

This refers to the Exact Match, which is FALSE.
This term refers to the closest possible match.
Excel VLOOKUP Tutorial Step 7) Press Enter!
To inform the cell that the function has been successfully finished, press the “Enter” button. However, you will receive an error message as shown below because the cell H2i.e. has not been provided with any value. There is currently no employee code entered in the Employee Code field, which prevents the value from being looked up.

Excel VLOOKUP Tutorial
On the other hand, as soon as you enter any Employee Code into H2, it will provide the corresponding value, which is the Employee’s Salary.

Excel VLOOKUP Tutorial
Therefore, in a nutshell, what transpired was that I communicated to the cell, by means of the VLOOKUP formula, that the values that we are aware of are present in the column of the data that is depicted on the left, i.e., the column for Employee’s Code. Now you need to look through my lookup table or my range of cells and locate the value on the same row in the fourth column to the right of the table. More specifically, you need to locate the corresponding value (Employee’s Salary) in the same row of the Employee’s Code that corresponds to that value.

The preceding example provided an explanation regarding the Exact Matches in VLOOKUP, in which the FALSE Keyword served as the final parameter.

VLOOKUP for Finding Closest Possible Matches (TRUE Keyword as the last parameter)
Imagine a scenario in which a table is used to compute discounts for customers who do not wish to buy exactly tens or hundreds of items in a single transaction.

As can be seen in the table below, one particular company offers price reductions on quantities of products ranging from one to ten thousand.

Vlookup function applied between 2 different sheets placed in the same workbook

Download the Excel file above for a tutorial on using the VLOOKUP function.

It is currently unknown whether the customer will purchase exactly one item or hundreds or thousands of products. In this particular instance, the Discount will be applied in accordance with the VLOOKUP’s Approximate Matches. To put it another way, we do not want to restrict them in their search for matches to the values that are already present in the column, which are 1, 10, 100, 1000, and 10000 respectively. The following are the stages:

1) Navigate to the area of the spreadsheet that requires the VLOOKUP function to be applied (cell reference ‘I2’), and then click there.

Tutorial on Using the VLOOKUP Function in Excel Step 2 Enter “=VLOOKUP()” in the cell. Fill in the set of Arguments for the preceding instance into the parentheses here.

Tutorial on the VLOOKUP Function in Excel Step 3: Enter the Arguments

Argument 1: Enter the Cell reference of the cell at which the value present will be searched for the corresponding value in the lookup table. This will be done at the cell at which you are entering the value.

Excel VLOOKUP Tutorial Step 4) The second part of the syntax for using VLOOKUP is to select either the lookup table or the table array that will contain the data that VLOOKUP will search for. (In this particular scenario, you will want to select the columns Quantity and Discount.)

Step 5 of the Excel VLOOKUP Tutorial: Argument 3 The column index in the lookup table that you want to be searched for the corresponding value represents the third argument.

Excel VLOOKUP Tutorial Step 5) Fourth and final argument would be the condition for approximately matching or exactly matching each other. In this particular instance, we are especially interested in finding the approximate matches (TRUE Keyword).

Excel VLOOKUP Tutorial Step 6) Press ‘Enter.’ When you enter any number in the quantity field, it will show you the discount that has been imposed based on the VLOOKUP formula’s Approximate Matches. The formula for VLOOKUP will be applied to the cell reference that was mentioned.

Excel VLOOKUP Tutorial NOTE: If you want to use TRUE as the last parameter, you can leave it blank and by default it will choose TRUE for Approximate Matches. This is important to keep in mind if you want to use TRUE as the last parameter.

The Vlookup function was applied to two separate sheets that were contained within the same workbook.
Let’s look at an example that’s comparable to the case scenario that was presented earlier. One workbook with two separate sheets has been provided for our use. One in which the Employee’s Code, Employee’s Name, and Employee’s Designation are all given. On another sheet, you’ll find the Employee Code as well as the corresponding Employee Salary (as shown below).

SHEET 1:

Excel VLOOKUP Tutorial SHEET 2:

Excel File
Download the Excel file located above.

Now the goal is to view all of the data on a single page, namely Sheet 1, as shown in the following example:

Tutorial for Excel’s VLOOKUP Function The VLOOKUP function can assist us in aggregating all of the data so that we can view the Employee Code, Name, and Salary in a single location or sheet.

The Employee’s Salary is listed in Sheet 2, which is to be searched by VLOOKUP, and the reference of the Column index is 2. As a result, we will begin our work on Sheet 2, as this sheet provides us with two arguments of the VLOOKUP function, namely: the Employee’s Salary is listed in Sheet 2, which is to be searched by VLOOKUP, and the reference of the Column index is 2. (as per the lookup table).

Excel VLOOKUP Tutorial
In addition to this, we are aware that we need to locate the employee’s salary that corresponds to the Employee’s Code.

Excel VLOOKUP Tutorial
In addition, the data begins in the A2 position and continues through the B25 position. Therefore, that would be the lookup table that we use or the table array argument.

Step 1) Select sheet 1 from the drop-down menu, and then enter the respective headings in the format displayed.

Tutorial on Using the VLOOKUP Function in Excel Step 2) Navigate to the cell in which you want to apply the VLOOKUP function and click on it. In this instance, it would be the cell next to Employee’s Salary, and the reference to that cell would be F3.

Excel VLOOKUP Tutorial
To use the Vlookup function, type =VLOOKUP in the box ().

Step 3) Enter the cell reference that contains the value that needs to be searched for in the lookup table in the “Argument 1” field. In this particular instance, the reference index is denoted by the letter ‘F2,’ and it will hold the Employee Code that needs to be matched with the appropriate Employee Salary in the lookup table.

Excel VLOOKUP Tutorial Step 4) Argument 2: In the second argument, we enter the lookup table or the table array, depending on which one is appropriate. On the other hand, in this particular instance, the lookup table is actually located in a different sheet within the same workbook. Therefore, in order to construct a relationship, we are required to enter “Sheet2” as the address of the lookup table! A2:B25 – (A2:B25 refers to the lookup table in sheet 2)

Step 5 of the Excel VLOOKUP Tutorial Argument 3: The third argument refers to the column index of the column that is present in the Lookup table where the values should be present.

Excel VLOOKUP Tutorial
Excel VLOOKUP Tutorial Step 6) The fourth and final argument concerns either the exact matches (which would be false) or the approximate matches (TRUE). In this particular instance, we would like to retrieve the exact matches that pertain to the Employee’s Salary.

Step 7 of the Excel VLOOKUP Tutorial Press the Enter key, and when you enter an employee’s code into the corresponding cell, you will be returned with that employee’s corresponding salary.

Conclusion

The operation of VLOOKUP Functions was described using the aforementioned three examples. You are free to experiment with using more instances. VLOOKUP is a significant feature that is available in Microsoft Excel that enables you to manage data in a more effective manner.