Excel training videos

Lookup functions using nested index and match functions

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-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 (below)
MATCH FUNCTION (below)
index-function match-function

INDEX(array,row_num,column_num)

Array is a range of cells or an array constant.

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.



Further interesting links

Lookup functions using nested index and match functions