Home » Excel Templates » How To Use Vlookup In Excel 2016: A Comprehensive Guide

How To Use Vlookup In Excel 2016: A Comprehensive Guide

Monday, July 24th 2023. | Excel Templates

Excel 2016 VLOOKUP, Part 01 YouTube from www.youtube.com

Introduction

If you are an Excel user, you are probably familiar with VLOOKUP, one of the most popular functions in Excel. VLOOKUP is a powerful tool that allows you to search for a specific value in a table and return a corresponding value from the same row. In this article, we will show you how to use VLOOKUP in Excel 2016.

Sample 1: Basic VLOOKUP Function

To use VLOOKUP, you need to have a table with data. Let’s say you have a table with employee names and their salaries. You want to find out the salary of a specific employee, John. Here’s how you can do it: 1. Open a new Excel worksheet. 2. Enter the employee names and their salaries in two separate columns. 3. Click on the cell where you want to enter the VLOOKUP function. 4. Type “=VLOOKUP(“. 5. Click on the cell that contains the name you want to find the salary for (in this case, John). 6. Type a comma “,”. 7. Select the range of cells that contains the table (including the headers). 8. Type a comma “,”. 9. Type the column number that contains the salary (in this case, 2). 10. Type a closing parenthesis “)” and press Enter. The cell will now display the salary of John.

Sample 2: Using Wildcards

Sometimes, you may not know the exact value you are looking for. In such cases, you can use wildcards to search for a value. Let’s say you want to find the salary of an employee whose name starts with “J”. 1. Follow steps 1 to 3 from Sample 1. 2. Type “=VLOOKUP(“. 3. Type “*J” (without quotes) instead of the name John. 4. Type a comma “,”. 5. Select the range of cells that contains the table (including the headers). 6. Type a comma “,”. 7. Type the column number that contains the salary (in this case, 2). 8. Type a closing parenthesis “)” and press Enter. The cell will now display the salary of the first employee whose name starts with “J”.

Sample 3: Using Exact Match

By default, VLOOKUP searches for an approximate match. This means that if the value you are looking for is not in the table, it will return the closest match. However, you can also use exact match to find an exact value. Let’s say you want to find the salary of John, but you want it to return an error if John’s name is not in the table. 1. Follow steps 1 to 3 from Sample 1. 2. Type “=VLOOKUP(“. 3. Click on the cell that contains the name you want to find the salary for (in this case, John). 4. Type a comma “,”. 5. Select the range of cells that contains the table (including the headers). 6. Type a comma “,”. 7. Type the column number that contains the salary (in this case, 2). 8. Type “,FALSE” (without quotes). 9. Type a closing parenthesis “)” and press Enter. The cell will now display the salary of John if his name is in the table, or return an error if it is not.

Tips for Using VLOOKUP

1. Always make sure that the table you are searching in is sorted in ascending order. 2. If you are using wildcards, make sure that the value you are searching for is unique in the table. 3. If you are using exact match, make sure that the value you are searching for is in the table.

Frequently Asked Questions (FAQ)

1. What is VLOOKUP?

VLOOKUP is a function in Excel that allows you to search for a specific value in a table and return a corresponding value from the same row.

2. What can I use VLOOKUP for?

You can use VLOOKUP to find data in a table, such as employee salaries, product prices, and customer information.

3. How do I use VLOOKUP?

To use VLOOKUP, you need to have a table with data. Follow the steps outlined in the Sample section of this article.

4. What is the syntax for VLOOKUP?

The syntax for VLOOKUP is “=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])”.

5. What is the range_lookup parameter?

The range_lookup parameter is optional and specifies whether you want an approximate match (TRUE) or an exact match (FALSE).

6. How do I avoid errors when using VLOOKUP?

Make sure that the table you are searching in is sorted in ascending order, and that the value you are searching for is unique in the table.

7. Can I use VLOOKUP with multiple criteria?

Yes, you can use VLOOKUP with multiple criteria by using the CONCATENATE function to combine the criteria into one cell, and then using that cell as the lookup value.