How to Use VLOOKUP in Excel? A Step-by-Step Guide

What Is VLOOKUP in Excel? 

VLOOKUP is a powerful function in Excel that searches for a value in the first column of a range (table or array) and returns a corresponding value in the same row from a specified column. The "V" in VLOOKUP stands for "Vertical," indicating that it searches vertically (downward) in a table.

Here's the basic syntax of the VLOOKUP function:

• lookup_value: This is the value you want to search for in the first column of the table.
• table_array: This is the table or range of cells where you want to perform the lookup. The first column of this range should contain the values you're searching for.
• col_index_num: The column number in the table from which you want to retrieve the corresponding value. For example, if the value you're looking for is in the first column of the table_array and you want to return a value from the third column, you would specify 3.
• [range_lookup]: This is an optional argument specifying whether you want an exact or approximate match. If set to TRUE or omitted, Excel will search for an approximate match (closest match). If set to FALSE, Excel will search for an exact match.

For example, if you have a table of employee information where column A contains employee IDs, column B contains employee names, and you want to find the name of an employee with a specific ID, you could use VLOOKUP like this:

This would search for "Employee ID" in the first column (A2:A10) and return the corresponding name from the second column (B2:B10). The FALSE argument indicates an exact match.

How to Use VLOOKUP in Excel?

Using VLOOKUP in Excel is relatively straightforward. Here's a step-by-step guide:

1. Prepare your data: Ensure that your data is organized in a tabular format where the value you want to look up is in the leftmost column of your table.

2. Determine what you want to look up: Identify the value you want to search for in the leftmost column of your table.

3. Select the cell where you want the result to appear: Click on the cell where you want the result of the VLOOKUP function to be displayed.

4. Enter the VLOOKUP function: Type "=" (equals sign) in the selected cell to start a formula, then type "VLOOKUP(". This will prompt Excel to expect the arguments for the VLOOKUP function.

5. Enter the arguments:

• lookup_value: Enter the value you want to search for.
• table_array: Select the range of cells that make up your table, including the column that contains the lookup value and the columns from which you want to retrieve data.
• col_index_num: Enter the column number from which you want to retrieve data. Count from the leftmost column in the table_array. For example, if you want to retrieve data from the third column, enter "3".
• [range_lookup]: Enter either TRUE for an approximate match or FALSE for an exact match. This argument is optional. If omitted, Excel assumes TRUE by default.

6. Close the parentheses: After entering the arguments, close the parentheses ")".

7. Press Enter: Press Enter on your keyboard to execute the formula. Excel will perform the VLOOKUP and display the result in the selected cell.

8. Drag or copy the formula: If you need to perform the same lookup for multiple cells, drag the fill handle (a small square at the bottom-right corner of the cell with the formula) or copy and paste the formula into other cells.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program

How to Do VLOOKUP in Excel With Two Spreadsheets?

Performing a VLOOKUP between two separate spreadsheets in Excel is similar to using VLOOKUP within a single spreadsheet. Here's a step-by-step guide:

1. Open both spreadsheets: Open the Excel files that contain the data you want to work with. Arrange the windows so you can see both spreadsheets simultaneously.
2. Identify the lookup value: Determine the value you want to search for in one spreadsheet (let's call this Spreadsheet A).
3. Select the cell for the VLOOKUP formula: Go to the spreadsheet where you want the result to appear (let's call this Spreadsheet B) and select the cell where you want the result of the VLOOKUP function to be displayed.
4. Enter the VLOOKUP formula: Type "=" (equals sign) in the selected cell to start a formula, then type "VLOOKUP(". This will prompt Excel to expect the arguments for the VLOOKUP function.
5. Enter the lookup value: Switch to Spreadsheet A and click on the cell that contains the lookup value you identified in step 2. This will automatically populate the lookup value argument in the VLOOKUP function.
6. Enter the table array: Switch back to Spreadsheet B and enter the table array argument of the VLOOKUP function. Click and drag to select the range of cells that make up the table in Spreadsheet B, including the column that contains the lookup value and the columns from which you want to retrieve data.
7. Enter the col_index_num: Enter the column number from which you want to retrieve data in Spreadsheet B. Count from the leftmost column in the table array.
8. Enter the [range_lookup] argument: Optionally, enter TRUE for an approximate match or FALSE for an exact match.
9. Close the parentheses and press Enter: After entering the arguments, close the parentheses ")" and press Enter on your keyboard to execute the formula. Excel will perform the VLOOKUP and display the result in the selected cell in Spreadsheet B.
10. Review and adjust: Review the result of the VLOOKUP formula to ensure it is correct. If necessary, adjust the formula or cell references as needed.
11. Repeat as necessary: To perform additional VLOOKUPs between the two spreadsheets, repeat the process for each lookup value.

How to Do VLOOKUP in Excel With Two Workbooks?

Performing a VLOOKUP between two different workbooks in Excel is quite similar to doing it within the same workbook or between two separate spreadsheets. Here's a step-by-step guide:

1. Open both workbooks: First, open the Excel workbooks that contain the data you want to work with. Make sure both workbooks are open and visible on your screen.
2. Identify the lookup value: Determine the value you want to search for in one workbook (let's call this Workbook A).
3. Select the cell for the VLOOKUP formula: Switch to the workbook where you want the VLOOKUP result to appear (let's call this Workbook B). Select the cell where you want the result of the VLOOKUP function to be displayed.
4. Enter the VLOOKUP formula: Type "=" (equals sign) in the selected cell to start a formula, then type "VLOOKUP(". This will prompt Excel to expect the arguments for the VLOOKUP function.
5. Enter the lookup value: Switch to Workbook A and click on the cell that contains the lookup value you identified in step 2. This will automatically populate the lookup value argument in the VLOOKUP function.
6. Enter the table array: Switch back to Workbook B and enter the table array argument of the VLOOKUP function. Click and drag to select the range of cells that make up the table in Workbook B, including the column that contains the lookup value and the columns from which you want to retrieve data.
7. Enter the col_index_num: Enter the column number from which you want to retrieve data in Workbook B. Count from the leftmost column in the table array.
8. Enter the [range_lookup] argument: Optionally, enter TRUE for an approximate match or FALSE for an exact match.
9. Close the parentheses and press Enter: After entering the arguments, close the parentheses ")" and press Enter on your keyboard to execute the formula. Excel will perform the VLOOKUP and display the result in the selected cell in Workbook B.
10. Review and adjust: Review the result of the VLOOKUP formula to ensure it is correct. If necessary, adjust the formula or cell references as needed.
11. Save your work: Don't forget to save both workbooks after making changes.

Become an Expert in SQL, R, Python & More!

Business Analyst Master's ProgramExplore Course

Tips to Use VLOOKUP Function Efficiently

Using the VLOOKUP function efficiently can greatly improve your productivity when working with Excel. Here are some tips to help you make the most out of VLOOKUP:

• Organize your data: Ensure that your data is well-organized in tables with clear headers. This makes it easier to understand and work with when using VLOOKUP.
• Understand the structure of your data: Before using VLOOKUP, familiarize yourself with the structure of your data, including the location of the lookup value and the columns from which you want to retrieve data.
• Use named ranges: Instead of manually selecting the range of cells for the table_array argument, define named ranges for your data. This makes your formulas more readable and easier to maintain.
• Use absolute cell references: When copying your VLOOKUP formula to other cells, use absolute cell references ($) for the table_array argument to ensure the range does not change.
• Sort your data: VLOOKUP works best when sorted in ascending order by the lookup column. This allows Excel to find the value it's searching for quickly.
• Use exact match: Unless you specifically need an approximate match, always use FALSE or 0 for the [range_lookup] argument to ensure that VLOOKUP performs an exact match. This prevents unexpected results.
• Handle errors: Use error handling functions like IFERROR or IFNA to handle cases where VLOOKUP cannot find a matching value. This prevents your worksheet from displaying errors.
• Consider using INDEX/MATCH: While VLOOKUP is commonly used for vertical lookups, combining INDEX and MATCH functions can offer more flexibility and efficiency, especially when dealing large datasets.
• Check for data inconsistencies: Ensure your lookup values are consistent across your datasets. Data inconsistencies can lead to inaccurate results when using VLOOKUP.
• Test your formulas: Before relying on VLOOKUP for critical tasks, test them with sample data to ensure they work correctly.
• Use the VLOOKUP wizard: If you're unfamiliar with writing formulas manually, you can use the VLOOKUP wizard in Excel to guide you through the process step by step.

Common Errors in VLOOKUP Function 

While the VLOOKUP function in Excel is powerful, there are several common errors that users may encounter:

1. #N/A error: This error occurs when VLOOKUP cannot find the lookup value in the first column of the table_array. It may happen due to misspelled or missing values or when the data is not sorted in ascending order (if using an exact match).
2. #REF! error: This error occurs when the referenced range for the table_array argument is invalid or has been deleted or replaced. Check the range references to ensure they are correct.
3. #VALUE! error: This error occurs when one or more of the input arguments of the VLOOKUP function are incorrect. Check that the arguments are entered correctly and match the expected data types.
4. Incorrect column index: If the col_index_num argument exceeds the number of columns in the table_array, VLOOKUP will return an error. Double-check the column index number to ensure it is within the range of columns in the table_array.
5. Missing or incorrect [range_lookup] argument: If the [range_lookup] argument is omitted and not explicitly set to TRUE or FALSE, Excel assumes TRUE by default, which may not always be the desired behavior. Explicitly specify TRUE or FALSE to avoid unexpected results.
6. Using VLOOKUP with merged cells: VLOOKUP may not work correctly, especially if the lookup value is within a merged cell. Avoid using merged cells when working with VLOOKUP.
7. Using relative cell references: If you copy a VLOOKUP formula to other cells without absolute cell references (using $), the range references may change, leading to unexpected results or errors. Always use absolute cell references when necessary.
8. Data type mismatch: Ensure that the data types of the lookup value and the first column of the table_array match. For example, if the lookup value is a number, ensure it's being compared with numeric values in the table_array.
9. Trailing spaces or non-printable characters: Extra spaces or non-printable characters in the lookup value or the data can cause VLOOKUP to fail to find a match. Clean your data to remove any unnecessary characters.
10. Data inconsistencies: Inconsistent formatting or variations in data entry can lead to mismatches between the lookup value and the values in the table_array. Ensure data consistency to avoid errors.

Your Ultimate Gateway to Success

Purdue Post Graduate Program in Business AnalysisExplore Program

Limitations of VLOOKUP in Excel

While VLOOKUP is a versatile and widely used function in Excel, it does have some limitations:

• Only searches vertically: VLOOKUP can only search for a value in the leftmost column of a table and retrieve a corresponding value from a column to its right. It cannot search horizontally.
• Limited to exact or approximate matches: VLOOKUP can only perform exact or approximate matches. It cannot perform other types of matches, such as partial matches or wildcard matches, without additional manipulation.
• Single-column retrieval: VLOOKUP can only retrieve values from a single column in the table_array. If you need to retrieve data from multiple columns, you'll need to use multiple VLOOKUP functions or another approach like INDEX/MATCH.
• Performance issues with large datasets: VLOOKUP can become slow and inefficient when used with large datasets, especially if the table_array is not sorted in ascending order. In such cases, alternative methods like INDEX/MATCH or Excel's built-in lookup functions like XLOOKUP or INDEX/AGGREGATE may be more efficient.
• Inflexible table_array: The table_array argument in VLOOKUP must be a static range reference. If your data expands or contracts frequently, you'll need to update the range reference manually, which can be cumbersome and prone to errors.
• Limited error handling: VLOOKUP does not offer robust error handling options. It typically returns #N/A errors when it cannot find a match, which may not always be desirable. Users often need to incorporate additional functions like IFERROR to handle errors more gracefully.
• Sensitive to column rearrangement: If columns are added, deleted, or rearranged in the table_array, it can break existing VLOOKUP formulas. This can be a maintenance headache, especially in large and complex worksheets.
• Not case-sensitive by default: VLOOKUP is not case-sensitive, meaning it treats uppercase and lowercase letters as equivalent. This can lead to unexpected results if your data contains case-sensitive values.
• Limited to the first matching value: VLOOKUP only returns the first matching value it finds in the leftmost column of the table_array. If multiple occurrences of the lookup value occur, it retrieves the corresponding value from the first match.
• No support for arrays or multiple criteria natively: VLOOKUP cannot handle arrays or multiple criteria within a single function call. Users often resort to array formulas or helper columns to achieve this functionality, which can be complex and resource-intensive.

Become job-ready with a globally recognized Business Analyst Certification Course. Sign-up today and enrich your career

Conclusion

Mastering the VLOOKUP function in Excel can significantly enhance your ability to retrieve and analyze data efficiently. This step-by-step guide has provided a comprehensive overview of how to use VLOOKUP effectively, from understanding its syntax to applying it in real-world scenarios through practical examples. By following these instructions, you can harness the power of VLOOKUP to streamline your workflow, perform accurate data lookups, and make informed decisions based on your Excel data. With practice and exploration, you'll unlock the full potential of VLOOKUP and excel in your data analysis tasks, empowering you to become more productive and proficient in Excel.

Are you looking to enhance your skills as a business analyst and excel in data analysis tasks? Our Business Analyst Certification Training Course is the perfect resource for you! Just as mastering the VLOOKUP function in Excel can streamline your workflow and empower you to make informed decisions, this comprehensive course will equip you with the knowledge and tools you need to excel in business analysis.

FAQs

1. How do I use VLOOKUP on Excel?

VLOOKUP in Excel searches for a value in the first column of a specified range and retrieves a value from the same row in a designated column. To employ this function, type =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) into a cell. lookup_value is the value you're searching for, table_array is the range containing the data, col_index_num is the column number from which to retrieve the value, and [range_lookup] is optional, true for an approximate match or false for an exact match.

2. What are the 3 rules for VLOOKUP?

• The lookup value must be in the first column of the range specified in table_array.
• The table_array must not be altered during the lookup process; ensure it is fixed, possibly by using absolute references (e.g., $A$1:$D$100).
• If using an approximate match (true or omitted in the formula), ensure the first column is sorted in ascending order to prevent incorrect results.

3. What is the lookup function in Excel?

The LOOKUP function in Excel searches for a value in a range or an array and returns a corresponding value from another range or array. It has two forms: vector and array. The vector form LOOKUP(lookup_value, lookup_vector, result_vector) searches lookup_value in lookup_vector and returns the value from result_vector. The array form allows searching for a value in one row or column and returns a value from the same position in another row or column.

4. What is VLOOKUP and Hlookup?

VLOOKUP, or Vertical Lookup, locates a value in a table's first column and retrieves a corresponding value from the same row in a specified column. Conversely, HLOOKUP, or Horizontal Lookup, finds a value in the first row of a table and returns a matching value from the same column in a designated row. Both functions extract data by referencing a key value, with VLOOKUP applicable to data arranged vertically in columns and HLOOKUP suitable for data organized horizontally in rows.