In this blog, we’ll explore the formula of VLOOKUP in both Excel and Google Sheets and also its parameters. We will also use real-world examples to provide a clear understanding. Additionally, we’ll examine some limitations to give you a well-rounded view of this formula.
Table of Contents:
Wanting to learn Excel from the basic to expert level? Check out the Excel course provided by Intellipaat!
What is VLOOKUP in Excel?
The VLOOKUP function in Excel is a powerful formula used to search for a specific value in a column of data and return a corresponding value from that row. As the name “Vertical Lookup” suggests, it searches vertically specified columns to identify the desired value. This function proves particularly useful for extracting information from extensive datasets, including tables and databases, and can simplify tasks like searching, matching, and organizing data within a spreadsheet or across different worksheets.
Intellipaat provides an Advanced Certification in Microsoft Excel. Enroll now to get a professional certificate!
Known for its efficiency in retrieving data, VLOOKUP is extensively utilized in Excel and Google Sheets. While the core functionality remains the same, subtle syntax differences exist between the Excel and Google Sheets versions of the formula.
Let’s understand the formulas for both Excel and Google sheets:
Excel =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Google Sheets =VLOOKUP(lookup_value, range, index, [is_sorted])
Parameters
- lookup_value – This is the value you’re searching for within the specified column of your table. For instance, if you’re looking for a specific product code or name, this is what you’d input.
- table_array/range – This is the area in your spreadsheet that holds the information you’re seeking. This can be a single column, multiple columns, or an entire table.
- table_array/range – This signifies the column number within your table_array/range from which you want to retrieve data. For instance, if you want to fetch the price of a product and the price is in the third column of your table, you’d input ‘3’ here.
- [range_lookup/is_sorted] – An optional argument that specifies whether the lookup_value should be an exact match or an approximate match.
Rules/Limitations You Should Know before Applying VLOOKUP
When working with VLOOKUP in Excel, it’s crucial to be aware of the rules/limitations that could impact its performance. Below are some of the rules/limitations of VLOOKUP:
- VLOOKUP returns the first matching value it finds. If there are multiple instances of the lookup value, it only retrieves the first one.
- VLOOKUP only allows you to retrieve values from columns to the right of the lookup column. You can’t retrieve a value from a column to the left of the lookup column using VLOOKUP.
- VLOOKUP is case-insensitive in most versions of Excel. If your data has variations in case (e.g., “A” vs. “a”), it might affect the accuracy of matches.
- If the lookup value isn’t found in the table, VLOOKUP returns an error. You may need to handle this with error-checking functions like IFERROR.
- If the range or size of your lookup table changes, you’ll need to update the formula manually to reflect the new range.
Get 100% Hike!
Master Most in Demand Skills Now !
How to Use VLOOKUP in Excel
Let’s explore how to use the VLOOKUP function both within a sheet and across different sheets in Excel. We’ll walk through a step-by-step guide with a simple example to give you a clear understanding of using this function effectively:
VLOOKUP in the Current Sheet:
- Step 1 – Ensure your data is organized in a table format, like in the below image, with clear headers.
- Step 2 – Suppose you’re seeking to retrieve the salary of an employee named “James.” This name will serve as your lookup value and also pick a cell in your Excel sheet where you’d like to use VLOOKUP, just like in the image.
- Step 3 – The table range should include the entire data range, including the column with the lookup values and the columns containing the information you want to retrieve. Here, your table range goes from B5 to D15.
- Step 4 – The column index number determines the specific column in the table from which you wish to extract data. For example, if you’re seeking the salary information located in the third column, the corresponding column index number would be 3.
- Step 5 – To ensure that VLOOKUP finds the exact match of the employee name, set the range lookup option to FALSE to ensure VLOOKUP returns. This way, you’ll get the exact information you’re looking for based on the name or other data you entered.
VLOOKUP from Another Sheet
- Step 1 – To use VLOOKUP across different sheets, simply follow the same steps we discussed for sheet 1. On the other sheet, like in the below image, select a cell where you want to use the VLOOKUP function.
- Step 2 – Next, select the specific lookup value for which you require data. For instance, consider the scenario where the name “James” is on Sheet 2, and you aim to retrieve his salary data from Sheet 1. In this case, simply pick “James” as the lookup value to proceed with retrieving his relevant information.
- Step 3 – Once you’ve finalized your lookup value, return to “Sheet 1” and define the table range that covers all the data, ensuring it includes the column with your lookup values and the columns containing the desired information. In this case, your table range extends from cell B4 to D15.
- Step 4 – After choosing the range in sheet 1, just enter the column index number which determines the specific column in the table from which you wish to extract data. Here we are going to choose 3 as the salary column number is 3, and then we are going to set the range lookup option to FALSE to ensure VLOOKUP returns an exact match. By following these steps, you’ll obtain the precise information you’re seeking.
Check out Intellipaat’s guide on MS Excel Interview Questions to crack good interviews for data analysis jobs.
Exact Match and Approximate Match in VLOOKUP
VLOOKUP’s range_lookup parameter plays a crucial role in deciding whether the formula should pinpoint an exact match or find the closest match possible for the lookup value. Let’s understand both functionalities of VLOOKUP to grasp their significance:
Finding Exact Match using VLOOKUP
If range_lookup is set to FALSE (exact match), the formula will only return a result if the lookup value matches exactly the value in the first column of the table array. If no exact match is found, the formula will return an error.
Example:
- The scenario involves a product table within a sheet.
- We aim to use the VLOOKUP function to find the price of a product identified by the ID 104.
- For our current lookup, the ID we’re interested in is 104, which serves as our “lookup_value.”
- The data we’re referring to is within the range A2:C6 on the sheet, including both the IDs and their respective price information.
- Specifically, we’ll select column 3 (“col_indexnum”) since it contains the pricing details for the products.
- To ensure accuracy in retrieving the desired information, we’ll set the “range_lookup” as FALSE. This ensures that VLOOKUP finds and returns the exact value or information linked to the specified ID (104).
- This method allows us to use the range_lookup feature in VLOOKUP to retrieve the exact value for product ID 104.
Finding Approximate Match using VLOOKUP
If range_lookup is set to TRUE (approximate match), the formula will return the closest match to the lookup value in the first column of the table array. This means that if the lookup value is not found exactly, the formula will return the value from the next highest position in the column.
Example:
- Consider the sales table within an Excel sheet as our example.
- Our objective is to find the sales bracket for a certain amount using VLOOKUP in approximate match mode.
- Consider we want to find the sales bracket for $4800.
- In this case, $4800 serves as our “lookup_value.”
- The data range we’re interested in spans from A2:B6 on the sheet.
- This range contains sales brackets in one column and their respective commission rates in the adjacent column.
- We’ll select column 2 (“col_indexnum”) since it holds the commission rates we’re interested in.
- Now, to perform an approximate match, we’ll set “range_lookup” as TRUE
- By doing this, VLOOKUP will locate the nearest match for $4800 within the sales brackets and return the corresponding commission rate.
How to resolve Range issues using VLOOKUP
A common problem arises when users encounter the ‘#N/A’ error while attempting to apply the same formula throughout multiple cells in Excel by dropping down the cursor. What happens is when you drop down the cursor, the range also changes if you had F1, A1:B50 in the formula, when you move it to the 5th cell, it becomes F5, A5:B55. Let’s tackle this problem one step at a time to find a solution.
- Step 1 – In attempting to extend a formula across multiple cells, you may encounter the #N/A error, indicating a change in the formula range as observed in this instance.
- Step 2 – When you’re extending the formula downwards, the range values decrease with each cell. To keep the range locked for the formula, simply add a ‘$’, as shown in the image below.
- Step 3 – At this point, the range remains fixed while only the lockup value undergoes changes. This adjustment ensures there won’t be any issue with ranges in Excel and you will get the precise data.
Learnings from This Blog
Through this blog, we’ve discovered that the VLOOKUP in Excel is a versatile and powerful function that simplifies data analysis and retrieval. Knowing when to use it can significantly enhance efficiency, whether for merging datasets, aligning information, or simplifying complex searches. Mastering VLOOKUP not only saves time but also unlocks a multitude of possibilities for manipulating and analyzing data effectively within Excel.