Lookup functions using nested index and match functions

Lookup functions using nested index and match functions in Excel 2007

Lookup functions using nested index and match functions: Vlookup and Hlookup have their own limitations when searching for information from an Excel database. To overcome these limitations you can use lookup functions which are combinations of useful functions like ‘index’ and ‘match’. Nesting the match function within the index function, as shown in the video below, you can create a new lookup function that helps to extract interesting information from the data.

nested match function within index function

nested match function within index function

Example
Looks up Mukesh in column A and returns the value for Mukesh’s Salary in column B (27000).

Formula to look up a value in an unsorted range (INDEX function)

1. A2:B5: The entire range in which you are looking up values.

2. MATCH(“Mukesh”,A2:A5,0): The MATCH function determines the row number.

3. “Mukesh”: The value to find in the lookup column.

4. A2:A5: The column for the MATCH function to search.

5. 2: The column from which to return the value. The leftmost column is 1.

Index Function

Index Function

INDEX(array,row_num,column_num)
Array is a range of cells or an array constant.
In the example shown in the image we find Hari’s salary when we know both the row and column numbers. Mostly we may just have the name of an employee whose salary is to be figured out.

Match Function

Match Function

MATCH(lookup_value,lookup_array,match_type)
Lookup_value is the value you use to find the value you want in a table.
If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
In the image we find the row in which Hari’s salary is located.
Now using the Match function nested within the Index function we can easily and quickly find the salary of an employee if we know her name.

Further reading:

Using Excel’s Match function in a Lookup Formula

One thought on “Lookup functions using nested index and match functions

Leave a Reply

Your email address will not be published. Required fields are marked *