EXCEL TRICKS-USING INDEX-MATCH

Table of Contents

INTRODUCTION

INDEX-MATCH is the combination of two great functions INDEX and MATCH which are very useful in the practical usage of Excel. This combination helps us to find out the values, corresponding to any lookup value from the same or another table within a fraction of seconds.

It becomes very important to learn these tricks in order to be proficient in EXCEL.

Let us first review both of these functions individually, so that we can understand how they would work in conjunction to make our lookup better and faster.

INDEX FUNCTION:

INDEX FUNCTION returns the value of a cell or array in a specified relative location given by row no. and column no. in a selected range.

A relative position is a position with respect to the select LOOKUP_RANGE.

For example, suppose

We have five COLORS and five CODES in which we need to find out a value on a particular position.[ Data at a particular location specified by row and column ].

So we can use the INDEX function for this scenario and it’ll find out the item to be searched at a particular position.

Suppose there is a table of 4 columns and 4 rows. If we want to search that what element is present in a row no. 3 and column no. 4, we’ll take the help of INDEX FUNCTION.
If you want to have an in-depth understanding of INDEX FUNCTION by solving the examples. HERE IS THE LINK.

 MATCH FUNCTION:

MATCH FUNCTION looks up VALUE_TO_BE_FOUND in the given range and returns its relative position.

Relative position is the position with respect to the selected LOOKUP_RANGE.

e.g.

We have five COLORS and five CODES and we need to find out at which position, any specified color is present.

The lookup value will be the color code, and the lookup range will be the range of colors.

The result will be the relative position of the color with respect to the selection.

If you want to have an in-depth understanding of MATCH FUNCTION by solving the examples. HERE IS THE LINK.

Now if you notice, both the functions are almost opposite to each other. So it is possible to combine both of them to get better use of both the functions to lookup and retrieve the value from a big pool of data. Let us see, how we can use them.



COMBINING INDEX AND MATCH

Index and Match functions are opposite to each other.

Index function finds out the value of an item at a particular location in an array whereas Match function returns the relative position of an item after matching it from a given list.

So we can combine both the functions to retrieve the value from any column, against a lookup value which will be searched by the MATCH function for us. Let us try to put this in the form of a function.

Syntax is given in the next Section.

PURPOSE OF INDEX-MATCH IN EXCEL

The combined INDEX-MATCH functions can be used to pull out any value which is corresponding to the another lookup value from another set of data which may or may not be on the same sheet.

SYNTAX: INDEX-MATCH

The INDEX-MATCH FUNCTION will be used in the following format when used for lookup.

=INDEX( Array/Range from where the value is finally needed, MATCH(LOOKUP VALUE, Array in which the value will be found, match mode ), relative column number )


Array/Range from where the value is finally needed
This is the table from which the value will be extracted finally .

MATCH Lookup Value The unique value on the basis of which, we’ll find the desired value. 

Array in which the value will be found  This is the column where the value will be found / Column to be searched for.

Match Mode Different match modes of MATCH FUNCTION

 Relative Column Number The column number of the INDEX (first argument) which will be returned for the value after the matching has been done. 

EXAMPLE 1:INDEX MATCH SIMPLE LOOKUP

We have a data sample with the students having different marks in different subjects. We’ll try a simple lookup of the data through index match first.

The data given is shown in the picture.

STUDENTSMATHEMATICSSCIENCEARTSLANGUAGESOCIAL SCIENCE
ASHU8985966985
DAVID8596956754
JOE9656828387
CREG8589986967
RAM7579789158
PETER9678949796

We’ll try to find the student with 83 marks in LANGUAGE.

After that, we’ll find the person who got 67 marks too.

EXCEL: INDEX MATCH EXAMPLE 1

EXPLANATION:

We put the formula as

=INDEX(G7:G12,MATCH(83,K7:K12,0),1)

G7:G12 is the table from which we need the result. Right now we selected just the column from where we need the name.

Match started as it’d return us to the index.

83 is the value to be found.

k7:k12 IS THE ARRAY WHERE 67 WILL BE COMPARED WITH.

0 IS THE EXACT MATCH

The third argument of the INDEX function is the column number to return which is 1 as we have selected a single column.

The result appears as JOE which is correct.

After this, we change the lookup value to 67 and see that the result changes to David.

EXAMPLE 2:INDEX MATCH- COMPLICATED LOOKUP

DATA SAMPLE

Find out the students who have the highest marks in Science. The data is given as the following table 

STUDENTSMATHEMATICSSCIENCEARTSLANGUAGESOCIAL SCIENCE
ASHU8985966985
DAVID8596956754
JOE9656828387
CREG8589986967
RAM7579789158
PETER9678949796

Let us try to find out the highest marks in Science.

EXCEL: INDEX MATCH EXAMPLE 2

EXPLANATION

We have to find the highest marks in Science. For that, we need to lookup for the highest marks first which we can do with the help of LARGE FUNCTION.

So we put it inside the Match Function. The function used to solve the problem is

=INDEX(G26:G31,MATCH(LARGE(I26:I31,1),I26:I31,0),1)

G26:G31 is the array from which we need the output.

Match function is started.

We’ll lookup the value which is highest for which we’ll use the LARGE function.

I26:I31 is the array from which the value is to be matched.

0 is for the exact match.

1 in the index function is for returning the value of the first column.

The answer is shown as DAVID which is correct.  

CONFUSION CLARIFICATIONS

THE RELATIVE ROW AND COLUMN NUMBER

EXCEL: INDEX FUNCTION: ROW COLUMN NUMBER CLARIFICATION

A cell is filled with YELLOW COLOR which we want to find. Now let us see in how many ways we can get this value.

The row and column number of the INDEX FUNCTION is relative to the RANGE SELECTED.

If we select Table 1, the value to be found will be at the row no. 4 and column no. 3.

If we select Table 2, the value to be found will be at the row no. 4 and column no. 3 again.

If we select Table 3, the value to be found will be at the row no. 3 and column no. 2.

So all these three different notations result in the same result. So always take care of the range selected and row and column no. selected.

EXCEL: INDEX FUNCTION: ROW COLUMN NUMBER CLARIFICATION IMPLEMENTATION