Match Index Functions In Excel

in Technology

It is always merging two excel functions into one by saying match index. But I am going to tell you that these are two different functions and have their own properties. Later on these two functions can be combined to get a more appropriate and specific result from the table in excel.

Lot of Excel websites do a deprived job of explaining how to use INDEX MATCH.  I’m not saying my explanation is better, but here is easy way to remember how to use the formula.

 

I will start with MATCH Function.

This function looks for an item in a list and shows its position. It can be used with text and numbers. It can look for an exact match or an approximate match.

The given syntax showing the required and need in the function.

 

Syntax

=MATCH(WhatToLookFor,WhereToLook,TypeOfMatch)

 

There are three types of Match

 

It can be with 0, it can be with 1 or it can be with -1.

 

Now how these three type works and give output.

Using 0 will look for an exact match. If no match is found the #NA error will be shown. Using 1 will look for an exact match, or the next lowest number if no exact match exists. If there is no match or next lowest number the error #NA is shown. The list of values being examined must be sorted for this to work correctly. Using -1 will look for an exact match, or the next highest number if no exact match exists. If there is no exact match or next highest number the error #NA is shown. The list must be sorted for this to work properly.

 

Examples 1
Using the 0 option suitable for an exact match. The Ascending list gives the exact match. The Descending list gives the exact match. The Wrong Value list cannot find an exact match, so the #NA is shown.

 

 

Example 2

Using the 1 option suitable for an ascending list to find an exact or next lowest match. The Ascending list gives the exact match. The Descending list gives the #NA error. The Wrong Value list finds the next lowest number.

 

 

Example 3

Using the -1 option suitable for a descending list to find an exact or next highest match. The Ascending list gives the #NA error. The Descending list gives the exact match. The Wrong Value list finds the next highest number.

 

 

You have seen the operation of match function individually. You can extend the list change the figure’s values to see the more functionality of the Match function.

 

Index function in excel

We have seen that match function only matches the values from different cell and accordingly gives in the required cell. Index function is here to be operated so you can find the difference between Match Function and Index Function. But don’t forget that these two functions are required for a more complex data table to get the exact value.

This function picks a value from a range of data by looking down a specified number of rows and then across a specified number of columns. It can be used with a single block of data, or non-continues blocks.

 

 

Syntax

There are various forms of syntax for this function.

 

Syntax 1

=INDEX(RangeToLookIn,Coordinate)

This is used when the RangeToLookIn is either a single column or row. The Co-ordinate indicates how far down or across to look when picking the data from the range. Both of the examples below use the same syntax, but the Co-ordinate refers to a row when the range is vertical and a column when the range is horizontal.

 

 

Syntax 2

=INDEX(RangeToLookIn,RowCoordinate,ColumnColumnCordinate)

This syntax is used when the range is made up of rows and columns.

 

 

Syntax 3

=INDEX(NamedRangeToLookIn,RowCoordinate,ColumnColumnCordinate,AreaToPickFrom)

Using this syntax the range to look in can be made up of multiple areas. The easiest way to refer to these areas is to select them and give them a single name. The AreaToPickFrom indicates which of the multiple areas should be used. In the following example the figures for North and South have been named as one range called NorthAndSouth.

 

 

Make sure that you have learned the INDEX Function as well as MATCH Function. I am going to combine them in Match and Index into Index Match

 

We have learnt Match function and Index function. Now it is time to learn the combination of the two functions. This combination is called “Match Index”

Through this function we will match the values by the match function and operate them with index to get the required result from the excel table.

 

Here we go.

The tables below were used to by a bus company taking booking for bus tours. They need to allocate a bus with enough seats for the all the passengers. The list of bus sizes has been entered in a list.

The number of passengers on the tour is then entered. The =MATCH() function looks down the list to find the bus with enough seats. If the number of passengers is not an exact match, the next biggest bus will be picked. After the =MATCH() function has found the bus, the =INDEX() function has been used to look down the list again and pick out the actual bus size required.

 

 

Example 1

The tables below were used by a school to calculate the exam grades for pupils.

The list of grade breakpoints was entered in a list. The pupils scores were entered in another list.

The pupils scores are compared against the breakpoints. If an exact match is not found, the next lowest breakpoint is used. The =INDEX() function then looks down the Grade list to find the grade.

 

 

Example 2

This is an extended version of the previous example. It allows the names of products and the quarters to be entered. The =MATCH() function is used to find the row and column positions of the names entered.

These positions are then used by the =INDEX() function to look for the data.

 

 

I hope understanding of MATCH & INDEX functions individually it would be easy to learn combination of two functions. I added some conditional operator (i.e. if()) in the combination.
See it yourself.

One has to work hard and will be awarded good skills 🙂

Good luck

 

Leave a Reply

Your email address will not be published.

*