VLOOKUP in Excel: Formula, How to Use, Examples & More

Are you tired of spending endless hours searching for specific information in large Excel files? You’re not alone. Luckily, Excel’s VLOOKUP tool comes to the rescue, making data discovery much easier. Whether you’re a seasoned Excel user or a beginner, mastering VLOOKUP can greatly enhance your data analysis skills. This comprehensive guide provides all the knowledge you need to understand and effectively utilize Excel’s powerful VLOOKUP function. Get ready to streamline your data analysis process and boost your productivity with VLOOKUP.

What is VLOOKUP in Excel?

You may locate a value within a certain cell range using the potent “Vertical Lookup,” or VLOOKUP, function. It is a helpful tool for sorting and organising huge volumes of data since it pulls related data from the same row. VLOOKUP may streamline your procedures and help you save time while working with customer databases, sales records, or financial data.

VLOOKUP Formula

Let’s examine the formula structure of VLOOKUP to realise its full potential. The VLOOKUP function’s fundamental syntax is as follows:

• =VLOOKUP(range_lookup, table array, col index number, and lookup value)
• The value you want to search for is referred to as lookup_value.
• The range of cells in table_array that contain the data you’re looking for is called this.
• The column number from which you want to retrieve the results is specified by col_index_num.
• You can specify whether you want an exact or approximate match with the range_lookup argument.

Also Read: Step-by-Step Guide to Becoming a Data Analyst in 2023

How to Find an Exact Match Using VLOOKUP?

Here are the steps to find an exact match using VLOOKUP:

Let us take the example of an Excel sheet containing ID, Company and product, as depicted in Figure 1. 

Step 1

Type in the VLOOKUP function with specific details in any selected blank cell. For instance, we intend to find the product on ID 3. We will type in “=VLOOKUP(A3,A2:C6,3,FALSE)”. A3 here refers to product ID 3, A2:C6 is the cells covering up all the data, 3 is the column from where the value will be retrieved, and false is written to find an exact result. 

Step 2

The overall VLOOKUP and information will be seen as depicted in Figure 2. Press Enter and get the result, as seen in Figure 3.

How to Find an Approximate Match Using VLOOKUP?

To the above example, we will add a new column of sales per day at any small scale to depict the usage of the approximate match using VLOOKUP (Figure 4). 

The intention is to find a product with more than 80,000 sales per day. 

Step 1

Add a new cell with the sales per day and write in the desired number. For instance, the written value 80,000 as in figure 5. The value 80,000  is labelled to avoid confusion.  

Step 2

Since this value is not present in the sheet, the result will surely be the approximate result else no result with the display message ‘N/A’. So, now select the ranges (sales per day in this case) and sort them in ascending order. Select the column, further choose the filter option and select ‘sort smallest to largest’. 

Step 3

Write in the VLOOKUP formula with specifications (exhibiting as per this case). The function here will be =VLOOKUP(C10,A1:D6,4,TRUE). C10 will guide the function to the value for which results are desired, A1:D6 will be the overall cells comprising all the values, 4 is the product column form where the result will be taken and True will give the approximate result. 

Step 4

Press Enter, and we get the right product, which is a cold drink, as shown in Figure 8. 

How to Use VLOOKUP for Multiple Criteria?

The function multiple criteria are used to lookup for the values present in different columns. The displayed values can be added in the already added new column. The example will exhibit a column with the company and its sales per day. The lookup formula will be used to find the product with information on a specific company and its sales per day. Figure 9 represents the sheet with the stated information.

Step 1

Create the column shown in Figure 9 by selecting the header and clicking insert. 

Step 2

Type in =C2&”-”&D2 in B2 and drag it down to the list. It will create the concatenated list. The C2 and D2 have been typed here as the company and sales per day are represented in these specifically mentioned columns. 

Step 3

Now look for the product of the sales per day of Pepsi. For this, enter the company and sales per day of Pepsi in a blank cell. For instance, the B10 in this example represents Pepsi and C10 in this example represents 90000. The cells just above them are labelled to avoid confusion. Check Figure 11 for reference. 

Step 4

Write in the VLOOKUP credentials in the cell below the product. For this example, the formula will be =VLOOKUP(B10&”-”&C10,B1:E6,4,FALSE). B10&”-”&C10 indicate the columns representing Pepsi and sales per day of 90000. B1:E6 encaptures all the information containing cells, 4 refers to the column from where the value will be returned, and FALSE will guide the function to the exact value. The results will be as per the depiction in Figure 12. 

Tips to Use VLOOKUP Function Efficiently

• Sort your data: VLOOKUP requires the data to be sorted in ascending order for accurate results.
• Use absolute references: When using VLOOKUP in formulas, lock the lookup range and column index to prevent errors when copying the formula.
• Utilize named ranges: Assigning names to your lookup ranges can improve formula readability and make them easier to maintain.
• Combine with IFERROR: Wrapping your VLOOKUP formula with IFERROR helps handle potential errors gracefully, providing alternative outcomes or error messages.
• Explore wildcard characters: Using wildcard characters like asterisks (*) and question marks (?), you can perform more flexible searches and find variations of your lookup values.

Common Errors in VLOOKUP Function

VLOOKUP is a strong tool, but it is not error-proof. The following are some frequent errors to avoid:

• Incorrect range reference: Ensure the range you specify in the table_array parameter is correct and includes the lookup value.
• Mismatched data types: Ensure that the lookup value and the values in the lookup range have the same data type, whether text, numbers, or dates.
• Unsorted data: Remember to sort your data in ascending order to obtain accurate results with VLOOKUP.
• Missing exact match: If you’re searching for an exact match, explicitly set the range_lookup parameter to FALSE or 0.
• VLOOKUP across multiple sheets: To perform a VLOOKUP between different sheets, use the sheet name followed by an exclamation mark (!) before the range reference.

Examples of VLOOKUP Applications

To illustrate the versatility of VLOOKUP, let’s consider a few practical examples:

• Retrieving product details: Use VLOOKUP to find detailed product information based on their unique identifiers or SKUs.
• Sales analysis: Analyze sales data using VLOOKUP to fetch product prices, customer information, or regional sales figures.
• Employee database: Organize employee data and use VLOOKUP to quickly retrieve information such as department, job title, or contact details.

Conclusion

Mastering VLOOKUP in Excel opens up a world of efficient data analysis and management possibilities. You can become a proficient user of this powerful function by understanding its formula structure, utilizing different match types, and employing advanced techniques. With practice and exploration, you’ll find that VLOOKUP can streamline your work, save time, and provide valuable insights. So embrace VLOOKUP and excel in your Excel endeavors!

To learn more functions of excel, consider enrolling in our free Microsoft Excel program!

Frequently Asked Questions