Excel Tutorial: Introduction to VLOOKUP, INDEX and MATCH functions

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.

VLOOKUP

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.

The syntax for VLOOKUP is:

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

• 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

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

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.