Advance of Index Match

Index Match for Complex Lookups

INDEX function Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

MATCH function returns the relative position of an item in an array that matches a specified value in a specified order.

In the previous article, you got acquainted with the basics of index and match functions

Example 1:

We can find a specific value in Excel table with combined index and match.

Consider the table below:

We want to get Oliver’s age from the table above:

Name	Age
Oliver	=INDEX(A2:E10,MATCH(G4,A2:A10,0),MATCH(H3,A1:E1,0))

Here, a combination of index and match functions should be used.

Example 2:

We want to get Elijah’s height from the table above:

Name	Height
Elijah	=INDEX(A2:E10,MATCH(G4,A2:A10,0),MATCH(H3,A1:E1,0))

Example 3:

We want to get William’s weight from the table above:

Name	Weight
William	=INDEX(A2:E10,MATCH(G4,A2:A10,0),MATCH(H3,A1:E1,0))