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.
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.
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.
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.