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

avcontentteam 19 Jun, 2023 • 7 min read

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. 

Vlookup in excel
Figure 1: Excel sheet with ID, company and product.

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. 

 Figure 2: Representation of VLOOKUP function with relevant commands
Figure 2: Representation of VLOOKUP function with relevant commands

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.

 Figure 3: Representation of exact match using VLOOKUP

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). 

 Figure 4: Excel sheet showing data with sales per day
Figure 4: Excel sheet showing data with sales per day

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.  

 Figure 5: Representation of the new column with sales per day and exact number being looked for
Figure 5: Representation of the new column with sales per day and exact number being looked for

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’. 

 Figure 6: Method to sort the values in ascending order
Figure 6: Method to sort the values in ascending order

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. 

 Figure 7: Screen showing VLOOKUP function with ‘TRUE’ command for approximate match
Figure 7: Screen showing VLOOKUP function with ‘TRUE’ command for approximate match

Step 4

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

 Figure 8: Output from the VLOOKUP function
Figure 8: Output from the VLOOKUP function

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.

 Figure 9: The column ‘company and sales per day’ are in the B column
Figure 9: The column ‘company and sales per day’ are in the B column

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. 

 Figure 10: Picture showing concatenated list
Figure 10: Picture showing concatenated list

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. 

 Figure 11: Representation of value being searched for
Figure 11: Representation of value being searched for

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. 

 Figure 12: The desired result ‘cold drink’ on cell D10
Figure 12: The desired result ‘cold drink’ on cell D10

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

Q1. Can VLOOKUP work across multiple sheets in Excel?

A. Yes, VLOOKUP can search for values across different sheets by specifying the sheet name followed by an exclamation mark (!) before the range reference.

Q2. What should I do if VLOOKUP does not produce the results I expected?

Verify the following two times:
1. Make sure the data is arranged properly and in ascending order.
2. Verify the data types of the lookup and the values in the lookup range.

To achieve the appropriate match type (exact or approximate), ensure that the range_lookup option is correctly configured

Q3. Can I search for values in a column to the left of the lookup column using VLOOKUP?

A. No, by default, VLOOKUP only looks for values in the lookup range’s leftmost column. However, you can conduct a reverse lookup using the INDEX and MATCH methods.

Q4. Does VLOOKUP take the case into account?

A. By design, VLOOKUP does not take case into account. Both capital and lowercase letters have the same meanings. If you need a case-sensitive lookup, combine the EXACT function with VLOOKUP.

Q5. Can I use VLOOKUP with multiple lookup criteria?

A. While VLOOKUP is designed for a single lookup criterion, you can use advanced techniques like concatenation or the INDEX and MATCH functions to achieve multiple criteria lookups.

avcontentteam 19 Jun 2023

Frequently Asked Questions

Lorem ipsum dolor sit amet, consectetur adipiscing elit,

Responses From Readers

Recommended for you
  • [tta_listen_btn class="listen"]