In this blog post we will explore a feature that allows us to search for a value based on specific criteria. The VLOOKUP stands for vertical lookup and is utilized to search for information within a table. The VLOOKUP function is:
= VLOOKUP(lookup_value, table_array, col_index_num,range_lookup)
The inputs are defined as:
lookup_value – This is the search value found in the first column of the table array.
table_array – This is the table of data the VLOOKUP function searches
col_index_num – This value is the number of the column in the table array that contains the data to return once the row is determined from the match of the lookup value in the first column.
range_lookup – This logical value can be either True or False. False will return exact matches only, while True will return partial matches.
In this example we have a table of Total Sales for 6 different months. The search criteria will find the total sales for the month of June. The function is defined with the following values.
- The lookup_value is Cell B3 or June.
- The table_array is Table 1 or Cells B8:C14.
- The column number is 2 so the VLOOKUP function will pull the data from the second column (Total Sales) of the table from the row that correlates with June in the first column.
- The range lookup is FALSE, so the table will only display data if there is an exact match for “June”.
The exact match is the value of the Total Sales in June of $9,600.
This function is very powerful for looking up data vertically in large tables. This function can be further customized for tables with many data columns by selecting data by its column number.
You can download this sample spreadsheet file for this tutorial.