Site icon excelwrap

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

 An Example of an INDEX function

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

If you notice the formula carefully, you will see

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

 An Example of a MATCH function

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.

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.

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:

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.

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:

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.

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.

Exit mobile version