How to Vlookup in Excel
Excel is a powerful tool that can make your work easier and faster. One of the most useful functions in Excel is Vlookup. Vlookup is an Excel function that allows you to search for a specific value in a table and return a corresponding value from a different column in the same table. In this article, we will show you how to use Vlookup in Excel.
Step 1: Understanding the Vlookup Function
The Vlookup function in Excel searches for a value in the first column of a table, and then returns a value in the same row from a column you specify. The syntax for the Vlookup function is:
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Where:
- lookup_value: This is the value you want to search for in the first column of the table.
- table_array: This is the table where the values are stored. It should contain at least two columns of data.
- col_index_num: This is the number of the column you want to return a value from. The first column is 1, the second column is 2, and so on.
- range_lookup: This is an optional argument that specifies whether you want an exact match or an approximate match. If you enter “FALSE” or “0”, you will get an exact match. If you enter “TRUE” or “1”, you will get an approximate match.
Step 2: Creating a Table
The first step to using Vlookup is to create a table. Your table should have at least two columns of data. The first column should contain the values you want to search for, and the second column should contain the values you want to return.
For example, let’s say you have a table that contains the names of employees and their salaries. You want to be able to search for an employee’s name and then return their salary. To do this, you would create a table with two columns: “Employee Name” and “Salary”.
Step 3: Using the Vlookup Function
Now that you have your table set up, you can use the Vlookup function to search for a specific value and return a corresponding value from the same row in the second column.
For example, let’s say you want to find the salary of an employee named “John Smith”. You would enter the following formula in a cell:
=VLOOKUP(“John Smith”, A2:B10, 2, FALSE)
Where:
- “John Smith”: This is the value you want to search for in the first column of the table.
- A2:B10: This is the table where the values are stored. In this example, the table starts at cell A2 and ends at cell B10.
- 2: This is the number of the column you want to return a value from. In this example, the second column contains the salaries.
- FALSE: This specifies that you want an exact match.
The result of this formula will be the salary of John Smith.
Step 4: Using Vlookup with Multiple Sheets
You can also use Vlookup to search for a value across multiple sheets in a workbook. To do this, you need to specify the sheet name in the table_array argument.
For example, let’s say you have two sheets in your workbook: “Sheet1” and “Sheet2”. You want to be able to search for an employee’s name in both sheets and return their salary. To do this, you would enter the following formula in a cell:
=VLOOKUP(“John Smith”, Sheet1!A2:B10, 2, FALSE)
=VLOOKUP(“John Smith”, Sheet2!A2:B10, 2, FALSE)
The first formula searches for John Smith in Sheet1, and the second formula searches for John Smith in Sheet2.
Step 5: Using Vlookup with Wildcards
You can also use Vlookup with wildcards to search for a value that contains a specific character or string of characters. To use a wildcard, you need to use the “MATCH” function in combination with Vlookup.
For example, let’s say you have a table that contains the names of employees and their salaries, but the names are spelled differently. You want to be able to search for all names that contain the string “Smith”. To do this, you would enter the following formula in a cell:
=VLOOKUP(“*Smith*”, A2:B10, MATCH(“Employee Name”, A1:B1, 0), FALSE)
The “*” before and after “Smith” tells Excel to search for any name that contains the string “Smith”. The MATCH function is used to return the column number of the “Employee Name” column, which is where the search value is located.
Step 6: Using Vlookup with Error Messages
If Excel cannot find the value you are searching for with Vlookup, it will return an error message. You can use the “IFERROR” function to display a custom message instead of the error message.
For example, let’s say you want to search for an employee’s salary, but you are not sure if the employee’s name is spelled correctly. You would enter the following formula in a cell:
=IFERROR(VLOOKUP(“John Smit”, A2:B10, 2, FALSE), “Employee Not Found”)
If Excel cannot find the name “John Smit”, it will return the custom message “Employee Not Found” instead of the error message.
Conclusion
Using Vlookup in Excel can save you a lot of time and effort. By following these steps, you can easily search for values in a table and return a corresponding value from a different column. Whether you are working with a single sheet or multiple sheets, Vlookup is a powerful tool that can help you work more efficiently.