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:

Employee Name | Salary |
---|---|

John | 50000 |

Jane | 60000 |

Bob | 55000 |

Alice | 65000 |

Mark | 70000 |

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.

- Using VLOOKUP with approximate match:

Suppose you have the following table:

Product Name | Price |
---|---|

Apples | 2.00 |

Bananas | 1.50 |

Oranges | 2.50 |

Grapes | 3.00 |

Pineapple | 4.00 |

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.

- Using VLOOKUP with wildcard characters:

Suppose you have the following table:

Product Code | Description |
---|---|

A-001 | Apples |

B-002 | Bananas |

A-003 | Oranges |

C-004 | Grapes |

A-005 | Pineapple |

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”.

- Using VLOOKUP with multiple criteria:

Suppose you have the following table:

Employee Name | Department | Salary |
---|---|---|

John | Sales | 50000 |

Jane | Marketing | 60000 |

Bob | Sales | 55000 |

Alice | HR | 65000 |

Mark | Sales | 70000 |

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.

- Using VLOOKUP with an IF statement:

Suppose you have the following table:

Product Name | Price | Discount |
---|---|---|

Apples | 2.00 | 0.1 |

Bananas | 1.50 | 0.05 |

Oranges | 2.50 | 0.2 |

Grapes | 3.00 | 0.15 |

Pineapple | 4.00 | 0.25 |

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