Excel has several lookup and reference functions. The main purposes of most of the plugins is to lookup some cell or cells from a set of data (usually presented in a table). Of these, the most popular ones are VLOOKUP, INDEX and MATCH. Usually INDEX and MATCH are used in combination as a replacement to VLOOKUP.
This post will walk you through the basics syntax of the functions. In a future post, we’ll use them to lookup some values.
You can use the VLOOKUP function to look up the value of a cell from a range of cells, usually within a table. A key limitation of this function is that the value that you are trying to match must sit in the first column of the table.
lookup_value: The value to search in the first column of the table or range. This is a required parameter
table_array: The range of cells that contain the data. This is a required parameter
col_index_num: The number of the column of the table from which the matching value has to be returned. The range needs to be between 1 and the number of rows of the tables
range_lookup: TRUE or 1 or omitting this argument will cause VLOOKUP to return an exact match only if the data in the column is arranged in alphabetical order. If there is no exact match, VLOOKUP will return the value in the last row. FALSE or 0 will cause VLOOKUP to return an exact match if available. Values do not have to be arranged alphabetically. This parameter is optional
Index function takes two forms, Array and Reference. We’ll be using the Array form, so I’ll focus on that form in this tutorial.
The syntax for INDEX function is:
INDEX(array, row_num, [column_num])
array: A range of cells or an array constant. This parameter is required
Row_num: Selects the row in array from which to return a value. If row_num is omitted, column_num is required. This parameter is required
Column_num: Selects the column in array from which to return a value. If column_num is omitted, row_num is required. This parameter is optional
MATCH searches for a data point through a range of data to return the relative position of the same.
The syntax for MATCH function is:
MATCH(lookup_value, lookup_array, [match_type])
lookup_value The value that you want to match in lookup_array. This is a required parameter
lookup_array The range of cells being searched. This is a required parameter
match_type The number -1, 0, or 1. The parameter is optional and if omitted, the default value for this argument is 1. 0 returns the exact match, while -1 returns the smallest value that is greater than or equal to lookup_value and 1 returns the largest value that is less than or equal to lookup_value
That’s it folks! This post should have given you an overview of the three functions. In the next post, we’ll try looking up some values using VLOOKUP, INDEX and MATCH.
Ajay is an investment banker by profession and a die-hard blogger by passion. He is the owner and chief editor of Techtites. In his free time, he also blogs at AjayDSouza.com, authors a few WordPress plugins and operates Sir Arthur Conan Doyle