VLOOKUP function to perform lookups in Excel

In this blog post, we will explore VLOOKUP function in Excel, and see how they can help you find the information you need. The VLOOKUP function is one of the most basic and widely used lookup functions in Excel. It allows you to search for a value in the first column of a table, and return a value from another column in the same row. The syntax of the VLOOKUP function is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The arguments of the VLOOKUP function are:

Table
Argument Explanation
lookup_value The value that you want to find in the first column of the table. This can be a cell reference, a text, a number, or a logical value.
table_array The range of cells that contains the table of data. The first column of this range should contain the lookup value, and other columns should contain the return values.
col_index_num The column number in the table_array from which you want to return a value. The first column is 1, the second column is 2, and so on.
[range_lookup] (Optional argument) If you want an exact match, use FALSE; if you want an approximate match, use TRUE.

For example, suppose you have a table of products and their prices, and you want to find the price of a product of a specific product name. You can use the VLOOKUP function to do this, as shown below:

In this example, the formula in cell C2 is:

=VLOOKUP(H2,A2:B17,2,FALSE)

This formula searches for the product name in cell H2 in the first column of the table in the range A2:B17, and returns the price from the second column of the same row. The last argument is FALSE, which means that the function will return an exact match of the product ID. If the product ID is not found in the table, the function will return an #N/A error, for which you can use IFNA function with VLOOKUP function to replace #N/A Error.

VLOOKUP function with other formulas

In the below example, we will calculate the total units sold of a product and calculate total sales amount of the same product.

To calculate total units sold: 

=COUNTIF(A1:B17, VLOOKUP(H2, A1:B17,1,FALSE))

To calculate total sales amount:

=SUMIF(A1:B17, VLOOKUP(H2,A1:B17,2,FALSE))

NOTE: Convert your data to a table (select your data range and click CTRL+T) so that whenever you feed a new sales entry the range of table array will be automatically updated.

Some of the advantages of using the VLOOKUP function are:

  • It is easy to use and understand, especially for beginners.
  • It can handle both exact and approximate matches, depending on your needs.
  • It can work with text, numbers, and logical values.
  • It can be use with other functions as well.
  • It can use wildcards (* and ?) to find partial matches, which can be useful when dealing with text data.

Some of the limitations of using the VLOOKUP function are:

  • It can only look up values from left to right, which means that the lookup column must be the first column in the table, and the return column must be to the right of the lookup column.
  • It requires that the lookup values are sorted in ascending order.
  • It can only return one value per lookup, which means that you cannot return multiple values from the same row.
  • It can be affected by changes in the table structure, such as inserting or deleting columns, which may change the column index number.
  • It is case-sensitive, i.e. the value in the lookup cell should match exactly with the value in the table array, without any trailing spaces.
  • It considers only the first occurrence of a value and ignores subsequent identical entries, which can lead to incomplete or inaccurate results.

Conclusion

In this blog post, we have learned about one of the most common and useful lookup functions in Excel, VLOOKUP. We have seen how this function can help us find the information we need from a table of data, based on some criteria or values. I hope that this blog post has helped you understand the logic behind this function. Thank you for reading and happy learning!

Comments

Post a Comment

Popular posts from this blog

XLOOKUP Function in Excel

Image
In our previous blog post we have already learned about 2 most useful lookup functions: VLOOKUP and HLOOKUP . In this blog post, we’ll explore what XLOOKUP is, its syntax, why you should use it, its pros and cons, and provide some practical examples. The  XLOOKUP  function revolutionize the way we search and retrieve data. XLOOKUP function is a modern and versatile replacement for traditional lookup functions in Excel. It allows you to find things in a table or range by row, regardless of whether the return column is on the left or right side. It’s like a search engine for your Excel data. Here’s what you need to know: Syntax : =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) Arguments : lookup_value : The value you want to search for. lookup_array : The range or array to search. return_array : The range or array from which to return results. [ if_not_found ] : (Optional) Specify what to return if no match is found. [ match_...
Read more

Introduction to Pivot Table in Excel - Advanced

Image
In the previous blog post, we learned the  Basics of Pivot Table  in Excel. In this blog post, we will explore some of the advanced features of pivot tables, such as slicers, timelines, calculated fields, pivot charts, managing relationships, etc. These features can help you change your existing pivot table and create different views of your data every time. Slicers Slicers are used to filter your data by selecting specific values from a field. For example in this data table , we have a column named Product (Crayons, Geometry Box, Paper, Pen, Stapler, Sticky Notes)  you can use a slicer to show only the data for a particular product or products.  Steps to add a Slicer: Select your pivot table ➡️ PivotTable Analyze ➡️ Click on "Insert Slicer" ➡️Choose the field for the slicer. A slicer window will appear, where you can select a field and a slicer will be inserted. You can select one option or multiselect by holding down the CTRL key while making your selections. You c...
Read more

Introduction to Pivot Table in Excel - Basics

Image
Pivot tables are one of the most powerful and useful features in Excel. Allowing us to quickly summarize, analyze, and explore large amounts of data in various ways. It is a special type of table that displays the summary of a data set and helps us in analyzing the data in seconds without knowing any formulas and functions. It consists of four areas: rows, columns, values, and filters. You can drag and drop different fields from your data set into these areas to create different views of your data. In this blog post, we will introduce the basics of pivot tables. Converting data to tabular data before inserting Pivot Table is an added advantage as this eliminates the need to frequently adjust the table range as new entries are added (select a cell from the data and click CTRL+T and then OK).  Additionally the following conditions need to be fulfilled: ✔ Each column should have unique headers, and the headers should be descriptive enough.       For ex. If Revenue...
Read more