Skip to content Skip to main navigation Skip to footer

INDEX and MATCH in Excel – Better alternative to VLOOKUP function

INDEX and MATCH function in Excel

INDEX and MATCH are the two different functions in Microsoft Excel. The combination of the INDEX and MATCH functions can be a better alternative to the VLOOKUP function. In this article, I will describe both the INDEX and MATCH functions with examples and also compare how the combination of these two functions works better than the VLOOKUP function.

INDEX and MATCH function

INDEX function

The INDEX function returns the value from a specified table as per the given reference.

Syntax of the INDEX function

=INDEX(array, row_num, [column_num])

Following are the arguments of the INDEX function

  • array (required) – The range of cells from where you want to return the value
  • row_num (required) – The row number in the table or range of cells from where you want your value to be returned.
  • column_num (Optional) – The value returned from the column number of the selected row.

 An Example of an INDEX function

I will give you a simple example here to understand the INDEX function. Look at the screenshot below.

INDEX function

If you notice the formula carefully, you will see

  • B3:F8 is the array or the range of cells where the INDEX function will search for the value.
  • 4 is the row number and 5 is the column number where the value is found.

MATCH function

The MATCH function lookup for a specified item in an array or a range of cells and returns the relative position of that item in the range.

Syntax of the MATCH function

=MATCH(lookup_value, lookup_array, [match_type])

Following are the arguments of the MATCH function

  • lookup_value (required) – The value you are looking for from the array or range of cells.
  • lookup_array (required) – The table or the array or the range of cells from where you are looking for the value.
  • match_type (optional) – -1,0. or 1. depends on how excel matches the value.

 An Example of a MATCH function

MATCH function example

As I have already said, the main purpose of the MATCH function is to find the relative position of data in a given range of cells or in a table. Look at the example of above I looked for the name John in a range of cells (C3:C8) it returned its position 3 as a result.

Now you already understand the basics of the INDEX and MATCH functions. What are the syntax and how to use them in Excel? Now I will describe how INDEX and MATCH function worked together in a formula. let’s get started.

Combination of the INDEX and MATCH function

I have prepared the following datasheet to give an example and explanation of the combination of the INDEX and MATCH functions. Let’s see the datasheet screenshot first.

INDEX MATCH

From the above table, I want to find the weight of John. Let’s see, how I can solve this by using a combination of the INDEX and MATCH functions.

NESTED INDEX MATCH function

Explanation of the formula

Look at the formula, the MATCH function is inside the INDEX function. The MATCH function will lookup for H3(John) in the range of cells C3:C8 (Name Column of the table). And the INDEX function will locate column number 5 from the range of cells or array B3:F8.

We can conclude with this formula:

  • The INDEX function needs a numeric position.
  • MATCH function finds that numeric position.

Two-way Lookup with INDEX and MATCH function

If you check the above example very carefully, you will find that only one data is dynamic(Name) another one is fixed (Weight). I mean column value is dynamic but the row is fixed. If you want both to be dynamic then also you can use the INDEX and MATCH functions together. I have prepared another datasheet for practice. Have a look at the screenshot.

Here, We have two inputs. Name and the Month. We want the sales data of any person of any month from the datasheet. Here our requirement is to find the sales value of a given employee and month. If you put the name and month we are expecting Excel to find the value of his sales in a given month. Let’s see, how it works with help of INDEX and MATCH functions.

INDEX MATCH MATCH

Explanation of the formula

We know the INDEX function lookup a value in an array or in a range of cell where the column position and the row position is given. Here MATCH function is nested inside the INDEX function to find that column and row position. As I have mentioned earlier the MATCH function is to find the position in a given row or column.

Now Check the formula we have used here

=INDEX(B3:F8),MATCH(I3,C3:C8,0),MATCH(I4,B2:F2,0))

We have used the MATCH function two times to find the position of Name and Month in the range of cells. When we use the MATCH function first time it looks for John (I3) in the Name column (C3:C8) and returns position 3 to the INDEX function. Again the next MATCH function looks for the month Feb (I4) in the range of row (B2:F2) and returns position 4 to the INDEX function.

As you know that the INDEX function needs array, row_num, and column_num to get the value in a cell. So, in our formula array is B3:F8 and it receives row_num from the first MATCH function and column_num from the second MATCH function.

Thus the INDEX function returns the sales value of John in the month of Feb. I hope you have understood how this formula works.

Why INDEX-MATCH is the better alternative to VLOOKUP

Those who are working with Excel data analysis should know the VLOOKUP function very well. If you are not aware of this function you can read below content to learn about the VLOOKUP function.

How to VLOOKUP in excel – step-by-step VLOOKUP function

If you do not know what the VLOOKUP function is and do not want to read the above link I can give a short description of VLOOKUP here.

VLOOKUP is a function that returns the lookup value from a range of cells or in an array vertically matching exact or approximate.

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

Arguments for the VLOOKUP function

The followings are the arguments for the VLOOKUP function:

  • lookup_value – It means the value you are looking for in a range of cells or tables.
  • table_array – The table from where you will look for the value.
  • col_index_num- The column number you will extract the value.
  • [range_lookup]- TRUE/FALSE, TRUE/1-approximate match, and FALSE/0- exact match. It is optional.

You can simply understand if you think in this way, while typing =VLLOKUP( what to look for, where to look for,  column number, and approx or exact match)

If we see the properties of the VLOOKUP function, we will find some shortcomings over the INDEX-MATCH function.

  • VLOOKUP function cannot look up from the left side but INDEX-MATCH can look up from both the left and right sides of a table or range of cells.
  • INDEX-MATCH formula is case-sensitive.
  • INDEX-MATCH formula can look up against multiple criteria.

Dear visitor, In this article I tried to guide you on how to use the function INDEX and MATCH in Excel. I hope, this content is will be very much helpful to you. If you face any problem understanding please feel free to post a comment below. I will try my best to reply to your question. Thank You.

0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *