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

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

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

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

2 thoughts on “Lookup functions using nested index and match functions

  1. Pingback: Using Index with Match in VBA for Lookups | Excel VBA Training Videos

Leave a Reply

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