Index & Match - a better alternative to VLOOKUP

Index and match are two different function in excel, and both have different and limited uses. However, together they create a function that is much more powerful than VLOOKUP. To understand how it works, lets first understand how both formulas work independently.

Index

=INDEX("area to consider", "row position", "column position")
                              or
=INDEX("Area where to check the value", "How much to move down in list", "How much to move to right in list")

Index formula returns the value at a given position. In simple terms, if we have a list of names as shown below. We can use Index formula to return the name as per their position in the list.

=Index(B1:B4,3)
result => John

Here John is at the position 3 in the list. If we change the number to 1, formula will return Raj as the result.

1	Raj
2	Shone
3	John
4	Sam

Note that the position is as per the range specified in the formula and not the row number in excel. In the below formula 4th item in the range B11 to B15 is at cell B14.

=index(B11:B15,4)

Index function can incorporate both row and column positions, i.e. we can specify which row and column to consider to return a value.

=Index(B1:D10,4,2)

Here we are specifying that the value that we require is at position of 2nd column and 4th row relatively. Note that the area/range selected cover multiple columns (B, C & D). this formula will return the value which is present at cell C4.

Match

=MATCH("What to search", "Where do you want to search it")

Match function does exactly opposite of Index i.e. it returns the position of a value in the range specified.

Referring to the same name table above if we want to identify at which position "John" is in the list, we can use the Match function as shown below.

=MATCH("John",B1:B4)
result => 3

So far we have been using hardcoded values in the formula. However connecting them to a cell which contains the reference value can make the formula dynamic, to return the result as per the changes in the value of cell without having to change the formula.

	A	B	C	D	E	F
1	Name	Code	Rank	Fav Colour	Row no	Column no.
2	John	J1	1	Red	3	2
3	Ram	R1	2	Blue
4	Shone	S1	3	Green	Name	Ram
5	Sam	S2	4	Violet

=INDEX(A2:D5,E2,F2)
returns => Mathew

=MATCH(F4,A2:A5)
returns => 3

Now that we know how both these formula works, we can make the magic formula that is Index & Match.

So how we are going to use it? We need to understand what we want to do first, so we want to obtain a value referring to a particular reference value. For example, we want to find what is the favourite colour of Ram in the above table.

G	H
Name	Ram
Column Name	Fav Colour

=INDEX("Area to consider", "Use Match to find the position of name", "Use Match to find position of desired column")

=INDEX(A1:D5,Match(H1,A1:A5),Match(H2,A1:D1))
result=> Blue

Breakdown =>
Match(H1,A1:A5) => 3 {"position of name in the list"}
Match(H2,A1:D1) => 4 {"position of Fav Colour in the headers"}

Index(A1:D5,3,4) => Blue {"Cell(3,4) contains value Blue"}

Here what makes this formula beautiful is that, what if we need to check what is the code for Ram instead of Fav Colour, by just changing the column name in the above table H2 cell will give us the desired result, without having to change the formula.

Index & Match is much more dynamic than VLOOKUP, unlike VLOOKUP that only search for values in left to right order in the data, Index & Match can look over the whole data i.e. we can search for values that lies on the left side of the reference cell, i.e. we could search for the name using the reference of Code.

Thanks for reading.

If you have any doubt or query, do write back and we will try to SOLVE THAT QUERY.