VLOOKUP is one of the most useful functions in Microsoft Excel. Every individual who works with a laptop or computer should know this function, as it can save a lot of time. VLOOKUP is helpful for identifying Desired Values, Duplicate values, Exact matches, and Approximate values. By using VLOOKUP, you can easily retrieve desired values or generate reports.

Let’s take a look at the VLOOKUP formula:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP with Example:

Assume that we have a small organization with a number of employees. Business management wants to look at the salary of a particular person. In this example, management wants to see the salary of Ryan.

In this case let’s see parameters with Examples:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• Lookup value: Value that you are trying to find in a first column
  Example : ‘Ryan’ or a cell that contains Ryan’s name (e.g., H1)

• Table Array: Range of the table (A1:D11)

• Col Index Number: The column number where you want to see the required value (in our case, it is ‘3’ because the salary is in the third column of the table)

• Range Lookup:
• FALSE or ‘0’ for an exact match
• TRUE or ‘1’ for an approximate match

To find Ryan’s salary:

Formula = VLOOKUP (H1,A2:D11,3,FALSE)

1. Lookup value: “Ryan” or the cell that contains Ryan’s name.
2. Table Array: The range of the table (A2:D11).
3. Column Index Number: Since we are looking for the salary, which is in the 3rd column, the number is 3.
4. Range Lookup: Enter ‘FALSE’ or ‘0’ for an exact match.

Close the brackets and press “ENTER.” You will get the result: 350000.

You can refer to the screenshot below to see that Ryan’s salary matches our VLOOKUP value.

To check other employees’ values, simply enter their names in cells H2, H3, H4, etc., and drag the formula from cell I1 downwards.

Alternatively:

Case 2: Management wants to see the salary of an employee in one cell.

Write the formula: =VLOOKUP("RAM", A2:D11, 3, 0)

Here the result is 100000 Matches with the salary of Ram (100000)

Case 3:

To enter the name in a single cell, use the following method:

1. Enter the employee name (e.g., MARTA) in cell I4.

1. The formula will then use the name in I4 to display the salary.

In this way, you can see the salary of any employee in a single cell by simply entering their name.

Case 4:

To check the Designation of employees, use the same formula but change the Column Index Number to ‘4’.