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.

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(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(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:

How to write VBA code for above formula. Please guide.

Hello Sir,

I need your help to write a VBA code. I have one value in column E. I need to find the value which may be in column B or D. Once the match is found the code should print 1 in column G. If a match is not found the code should print 0 in column G.

The data is in millions and a double vlookup takes huge amount of time to do the job.

My email address is achilleshashi@gmail.com

Dear sir,

My duplicate value like ID: 1,2,3 3,1 1,4 4 , in coulumn A and another duplicate value Name: ABC, AAA, CCC, ABC, CCC, AAA in column B and their amount 1200,1300,1111,,1522,4000 in Column C. Now i want to count ID with matching Name and sum Value of column C with amount.