Mastering VLOOKUP Function in Excel: Simplest Detailed Guide

What is VLOOKUP Function? Why is it Widely Used in Excel? Are There Any Limitations to VLOOKUP Function? Today's Mytour article will delve into the detailed usage of VLOOKUP function in Excel. Follow along to find the precise solutions.

What is VLOOKUP? 

This is an Excel function pre-programmed to compute and execute a specific feature. VLOOKUP function's tentative translation is 'Vertical Lookup.' Users can easily locate data, information in a vertical row within a table. Subsequently, it returns accurate information in the corresponding horizontal row. 

Mastering VLOOKUP function proficiency accelerates large-scale data computation. Moreover, the function finds extensive application in various operations such as: 

• Find product names
• Find prices
• Retrieve quantities based on product code, barcode,...
• Find employee names
• Sort, classify products
• Allocate employees to departments

Benefits of Using VLOOKUP Function

The main function of VLOOKUP saves time, effort in repairing formulas. If the computed value changes, this function will support users maximally. Generally, VLOOKUP function has a total of 3 reference types: 

• Absolute address reference
• Relative address reference
• Mixed reference

Guide to Using VLOOKUP Function in Excel 

For the function to work smoothly in Excel, you need to remember the precise steps of usage. Here we have detailed guidance on each step as follows: 

• Step 1: Identify the column of cells containing new data 
• Step 2: Enter the formula-containing function into the result cell 
• Step 3: Enter search data for the information you want to retrieve 
• Step 4: Enter the desired spreadsheet for the data to be placed 
• Step 5: Enter the number of data columns for Excel to return 
• Step 6: Proceed to enter an accurate or approximate search range 
• Step 7: Click “Done” to complete the function then enter a new column 

VLOOKUP Formula in Excel

When using the VLOOKUP function, you need to meet certain factors based on the standard formula. Specifically, the formula of the function is:

=VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup).

Where: 

• Lookup_value: The value to search for, can be entered directly/referenced from a cell in Excel spreadsheet 
• Table_array: The delimited table to search for products, employee names,... 
• Col_index_num: The ordinal number of the column to retrieve data from the user's table to search, counted sequentially from left to right.
• Range_lookup: Find exact or approximate match in the delimited Table_array. If the user skips, it defaults to 1. Where Range_lookup = 1 (TRUE) is approximate match and Range_lookup = 0 (FALSE) is exact match. 

You should note when copying the VLOOKUP function formula into other data cells. Use the $ sign to fix the Table_array. This will limit the search before declaration. You can press F4 after selecting the table or directly enter the $ sign (for example, $H$6:$J$13). 

Common Errors When Using VLOOKUP Function

When using the VLOOKUP function, you may encounter certain inevitable errors. To help readers master the function, here we have compiled some common errors as follows: 

Error #N/A

In case you don't find the value on the far-left column (in Table_array), the VLOOKUP function will display #N/A error. In this scenario, you need to use the MATCH function combined with the INDEX function.

If you don't find a result due to the #N/A error, it is highly likely that there is no data in the Table_array. Users should use IFERROR to change #N/A to another value.

For cases where there is certainly data in the Table_array but the VLOOKUP function cannot find it, you should check your reference data cells one by one. This will accurately identify cells that do not contain non-printable characters or hidden spaces. Also, ensure that all data cells adhere to the correct format. 

Error #REF!

This is a common error when using the VLOOKUP function. Most #REF errors occur when Col_index_num is greater than the number of columns in the Table_array. To fix the error, you need to check the formula so that Col_index_num is equal to or less than the number of columns in the Table_array.

Error #VALUE!

The #VALUE! error often occurs when Col_index_num in the formula is less than 1. Typically, column 1 is the lookup column, where column 2 is the first column to the right. Therefore, when using the VLOOKUP function, be sure to carefully check the value of Col_index_number in the formula. 

Error #NAME?

Surely during the use of the VLOOKUP function, you will encounter the #NAME? error at least once. This error mostly occurs if Lookup_value is missing double quotation marks ('). When searching for text format values, you need to use double quotation marks (') for Excel to understand the formula clearly. 

Notes When Using VLOOKUP Function

When using the VLOOKUP function, remember that the lookup value should be in the first column of the Excel range. If this criterion is met, the function will work accurately. Users need to specify TRUE if they want to receive approximately matched results. For exact matching results, use FALSE. 

The less information you need to search for, the more challenging it is to use the VLOOKUP function. Most users often use this type of function in a reusable spreadsheet such as a template. Each time you enter a product code, the system retrieves all the information about the corresponding product. Additionally, there are some points to note: 

• When range_lookup is omitted, this function allows approximate matching, users can use exact matching (if available)
• If the lookup column contains duplicate values, the VLOOKUP function will only match the first value
• The function only retrieves data from columns to the right in the first position of the table 
• VLOOKUP function is not case-sensitive 
• VLOOKUP function allows users to use wildcard characters 

How to Fix Errors When Using VLOOKUP Function in Excel

Do you want to understand and use the VLOOKUP function accurately? It's best to keep in mind some limitations of this type of function and useful troubleshooting methods. We have summarized them below:

Only supports reference from left to right

One limitation of the VLOOKUP function is that it operates from left to right. In practice, users can combine some formulas to make the function work in the opposite direction. For example, the Match & Index formula. However, this operation is quite complex and not straightforward. 

Efficient Operation with Unique Values 

When used, you need to be aware because the VLOOKUP function only cares about the first reference value. This type of function often overlooks other rows if they contain similar values. In cases where there is not much differentiation in the data, you need to 'bend the rules' a bit to make the function work accurately. The way to fix this error is to create a Pivot table with unique values. Then use it as usual. 

Fixing Column Reference Order 

Manually entering the column reference order numbers into the formula content can be cumbersome. Users can overcome this limitation by combining the Match function with VLOOKUP. 

Setting 'Approximate Match' by Default 

The condition to check the reference in the VLOOKUP function is always an optional factor. You can observe it through the symbol of two square brackets []. If you don't enter anything into those 2 symbols, the formula will automatically default to Approximate Match. 

To avoid inaccurate reference results when using, you need to adjust the VLOOKUP function. Set it to 'Exact Match' to get the most accurate reference results. 

Slowing down spreadsheet response time 

In reality, some users with large amounts of data have experienced crashes. The program's performance will decrease, and the best way to fix it is to replace the Paste Special command. 

Addressing Frequently Asked Questions

Why Use VLOOKUP in Excel?

• Specially assists users in searching vertically  
• Can be combined with functions like Sum, If, etc.
• Operates completely independently 

Differentiating VLOOKUP vs HLOOKUP

Most users often confuse VLOOKUP and HLOOKUP functions. Basically, the functions and usage of these two types of functions are quite similar. When operating, the HLOOKUP function also searches for a value in the top row of an Excel table. Then the HLOOKUP function returns the value in the same column that you specified in the table. 

So, what are the differences between the VLOOKUP and HLOOKUP functions? How to use them accurately. Here we have compiled detailed information from A to Z as follows: 

• The HLOOKUP function displays comparison values in the horizontal row of the data table
• The VLOOKUP function displays comparison values in the vertical column to the left of the data table 
• The letter H in the HLOOKUP function name stands for Horizontal
• The letter V in the VLOOKUP function name stands for Vertical

Which functions can VLOOKUP be combined with?

VLOOKUP with multiple conditions is an advanced function that integrates other functions into the basic command. When applied correctly, users can solve and support more issues. Specifically, there are some advanced formulas such as: 

• Nested VLOOKUP: Specializes in retrieving data from multiple spreadsheets/ranges, combining tables into one
• VLOOKUP and IF: Specializes in data lookup in a table/range based on one or more conditions
• VLOOKUP and MATCH: Specializes in cross-referencing 2 fields in a dataset
• VLOOKUP and Indirect: Specializes in automatically pulling data from different worksheets  

Conclusion 

Hopefully, after following this article, you have grasped some information about VLOOKUP function. If you want to use Excel in your work, this type of function will be very helpful. It's best to remember the information that Mytour shares to use the VLOOKUP function in Excel correctly.