VLOOKUP Excel Function: Quick and Easy Tutorial

Microsoft Excel provides various functions that ease our daily tasks. While dealing with big data, we often need to search for specific data. To do this, we can use a function called Vlookup Excel Function. This function is used to search specific data from a spreadsheet. When you are learning Excel or working on Excel as a teacher, employee, manager or any other role. Knowing the search function is essential for every role. 

This blog will help you gain a deep understanding of the workings of the Volookup Excel Function. After reading it, you will have all the answers to your queries. In this guide, Vlookup is explained from the basic to the advanced level. 

What is the Vlookup Excel Function?

Vlookup is the search function that Microsoft Excel provides for finding a specific value from a table. It is a straightforward function that can be learned quickly, and its syntax is straightforward to use. V in Vlookup stands for vertical, which means it searches the data from a table or spreadsheet vertically. Let’s understand the syntax of Vlookup. 

Syntax Of the Vlookup 

VLOOKUP (table array, col index number, lookup value, and [range lookup])

The following is the meaning of each syntax point:

• Lookup_value: This is the value you wish to look up or locate in the table of data.
• table_array: This is the range of cells in the spreadsheet that you want to search for more data in the future, as well as the lookup value.
• col_index_num: This is the table_array column number that you wish to obtain data from. The range’s first column has the number 1, the second has the number 2, and so forth.
• range_lookup (optional): Indicates if you seek an exact match using this optional input.
• TRUE: An approximative match will be returned by the function. The table’s first column needs to be arranged in ascending order.
• False: The feature shall

How VLOOKUP Works

When you use the VLOOKUP function, Excel starts by searching for the lookup_value in the first column of the table_array. Once it finds a match, it moves across the row to the column specified by col_index_num and returns the value from that column.

Example

Let’s say you have a table of products with their prices and stock levels like this:

Now, you want to find out the price of a “Coke” using VLOOKUP. Here’s how you would write the formula:

=VLOOKUP(“Coke”, A2:C4, 2, FALSE)

Breaking it Down:

• Lookup_value:” Coke” – this is what you’re searching for in the first column of the table.
• table_array: A2
  – this range includes the data you’re working with, where the first column contains the product names.
• col_index_num: 2 – this tells Excel to return the value from the second column in the table_array, which is the Price column.
• range_lookup: FALSE – this specifies that you want an exact match. If Excel cannot find “Coke” exactly, it will return an error.

Result: The function will return $1.15, which is the price of the Coke.

Common VLOOKUP Errors and How to Fix Them

Important Points to Remember

• The lookup_value must be in the first column of the table_array. If it’s in any other column, VLOOKUP will not work correctly.
• The col_index_num must be greater than or equal to 1 and not exceed the total number of columns in the table_array.
• If you use TRUE or omit the range_lookup argument, VLOOKUP will search for the closest match if an exact match is not found. However, for this to work, the data in the first column must be sorted in ascending order.
• If you use FALSE, VLOOKUP will only return an exact match. If there is no exact match, you will get an error (#N/A).

When you’re using the VLOOKUP function in Excel, you might run into a few common errors. Here’s a straightforward guide to help you understand these errors and fix them quickly.

1. #N/A Error

• Why It Happens: You’ll see the #N/A error when VLOOKUP can’t find the value you’re looking for in the first column of your data range. This usually happens if:
  • The value doesn’t exist in your table.
  • You’ve set the function to find an exact match (using FALSE as the last argument), but there’s no exact match.
  • There’s a mismatch in data types, like searching for a number when the data is actually stored as text.
• How to Fix It:
  • Check the Value: Make sure the value you’re looking for is actually in the first column of your data.

Use IFERROR or IFNA: To prevent the #N/A error from showing up, wrap your VLOOKUP function in an IFERROR or IFNA function like this:

=IFNA(VLOOKUP(“Coke”, A2:C4, 2, FALSE), “Not Found”)

• Now, if the value isn’t found, Excel will return “Not Found” instead of an error.
• Remove Extra Spaces: Hidden spaces or formatting issues can sometimes cause problems. Use the TRIM function to clean up any extra spaces.

2. #REF! Error

• Why It Happens: The #REF! error pops up when you specify a column number (col_index_num) that’s higher than the number of columns in your data range. For example, if your table has three columns and you ask for data from column 4, Excel won’t know what to do.
• How to Fix It:
  • Check the Column Number: Make sure the column number you’re using is within the range of your table. If your table has three columns, your column number should be 1, 2, or 3.

3. #VALUE! Error

• Why It Happens: You’ll get a #VALUE! Error if the column number (col_index_num) you’ve entered isn’t valid. This can happen if you accidentally type text instead of a number.
• How to Fix It:
  • Make Sure the Column Number is a Number: The column number should be a whole number like 1, 2, or 3. Avoid using letters or symbols.

4. #NAME? Error

• Why It Happens: The #NAME? The error occurs when Excel doesn’t recognize the function name, which usually happens due to a typo. It can also occur if you forget to put quotation marks around a text value you’re searching for.
• How to Fix It:
  • Double-Check Your Spelling: Make sure you’ve spelled “VLOOKUP” correctly.
  • Use Quotation Marks for Text: If the value you’re looking for is text, enclose it in quotation marks, like “Coke “

5. #NUM! Error

• Why It Happens: The #NUM! error shows up if the column number (col_index_num) is less than one or if you’ve entered an invalid number.
• How to Fix It:
  • Correct the Column Number: Ensure that the column number is a positive whole number within the range of your data.

6. Incorrect Results Due to Approximate Match

• Why It Happens: If you set the last argument of VLOOKUP to TRUE or leave it blank, Excel might return an approximate match instead of an exact one. This can lead to wrong results if the first column isn’t sorted in ascending order.
• How to Fix It:
  • Use FALSE for Exact Matches: Always set the last argument to FALSE to ensure VLOOKUP returns an exact match.
  • Sort Your Data: If you need an approximate match, make sure the first column of your table is sorted in ascending order.

Tips to Avoid VLOOKUP Errors

• Use Absolute References: When you’re copying the formula to other cells, use absolute references (like $A$2:$C$4) for your data range to prevent it from shifting and causing errors.
• Match Data Types: Make sure the data type of the value you’re searching for matches the data type in the first column of your table.
• Handle Errors Gracefully: Use IFERROR or IFNA to make your formulas more user-friendly by displaying custom messages instead of errors.

Final Words

As we conclude this topic, the Vlookup function in Microsoft Excel is a powerful search function that searches specific data from a table. While working on the Vlookup function, many challenges and errors can occur. That is why learning Vlookup is very important for beginner students. Microsoft Excel also provides us with other search functions, but Vlookup is the most used and important function.