## How to use Vlookup in Excel VLOOKUP is a function in Microsoft Excel used to look up and retrieve data from a table based on a search term. It stands for “Vertical Lookup” because it searches vertically down a specific column of a table for a match to the search term and returns a value from the corresponding row.

Basic VLOOKUP syntax:

The basic syntax of the VLOOKUP function is:

`=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)`

Where:

• `lookup_value`: The value you want to lookup.
• `table_array`: The range of cells containing the data you want to lookup in.
• `col_index_num`: The column number (starting from 1) of the value you want to return.
• `range_lookup`: Optional. Whether you want an exact match (FALSE) or an approximate match (TRUE).

Practice

Suppose you have the following table:

You want to look up the salary of the employee named “John”. To do this, you can use the following formula:

`=VLOOKUP("John", A2:B6, 2, FALSE)`

The result will be 50000, which is the salary of John.

1. Using VLOOKUP with approximate match:

Suppose you have the following table:

You want to look up the price of the product named “Aples” (with a typo). To do this, you can use the following formula:

`=VLOOKUP("Aples", A2:B6, 2, TRUE)`

The result will be 2.00, which is the price of Apples.

1. Using VLOOKUP with wildcard characters:

Suppose you have the following table:

You want to look up the description of the product whose code starts with “A-“. To do this, you can use the following formula:

`=VLOOKUP("A-*", A2:B6, 2, FALSE)`

The result will be “Apples”, which is the description of the product with the code “A-001”.

1. Using VLOOKUP with multiple criteria:

Suppose you have the following table:

You want to look up the salary of the employee named “John” who works in the “Sales” department. To do this, you can use the following formula:

`=VLOOKUP("John"&"Sales", A2:C6, 3, FALSE)`

The result will be 50000, which is John’s salary.

1. Using VLOOKUP with an IF statement:

Suppose you have the following table:

You want to look up the discounted price of the product named “Apples” if the discount rate is greater than 10%. To do this, you can use the following formula:

`=IF(VLOOKUP("Apples", A2:C6, 3, FALSE)>0.1, VLOOKUP("Apples", A2:C6, 2, FALSE)*(1-VLOOKUP("Apples", A2:C6, 3, FALSE)), VLOOKUP("Apples", A2:C6, 2, FALSE))`

The formula first checks if the discount rate of “Apples” is greater than 10%. If it is, the formula calculates the discounted price by subtracting the discount percentage from 1 and multiplying it by the original price. If the discount rate is less than or equal to 10%, the formula returns the original price.

In this case, the discount rate for “Apples” is 0.1, which is greater than 10%. The formula calculates the discounted price as follows:

=2*(1-0.1) = 1.80