MS Excel is used by businesses and researchers to store huge volumes of data which run into thousands of rows and columns. In such scenarios it becomes virtually impossible to manually find or search some specific values. To solve this problem, MS Excel provides us with LOOKUP Functions. The major functions under this category are:
3. Index and Match
In this article, we will discuss INDEX Function & MATCH Function with help of an example. INDEX and MATCH requires two functions, one nested inside another.
The combination of INDEX MATCH Function is used to overcome the drawbacks of VLOOKUP. It’s simply a more flexible way to look things up. Here are a few advantages over VLOOKUP:
- INDEX and MATCH can look left or right in a table, VLOOKUP can only look right.
- INDEX and MATCH can work with horizontal or vertical ranges, VLOOKUP can only use vertical ranges.
- INDEX and MATCH can work with data sorted in descending order, VLOOKUP can only match data sorted in ascending order.
- If you have a large set of data, INDEX MATCH can be much faster.
Syntax: INDEX(array, row_num, [column_num])
- Array Required. A range of cells or an array constant.
- If array contains only one row or column, the corresponding Row_num or Column_num argument is optional.
- If array has more than one row and more than one column, and only Row_num or Column_num is used, INDEX returns an array of the entire row or column in array.
- Row_num: (Required) Selects the row in array from which to return a value. If Row_num is omitted, Column_num is required.
- Column_num: (Optional) Selects the column in array from which to return a value. If Column_num is omitted, Row_num is required.
Syntax: MATCH(lookup_value, lookup_array, [match_type])
- lookup_value: (Required) The value that you want to match in lookup_array. For example, when you look up someone’s number in a telephone book, you are using the person’s name as the lookup value, but the telephone number is the value you want.
The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
- lookup_array: (Required) The range of cells being searched.
- match_type: (Optional) The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.
Example: We want to find out First_name based on Extension.
Step 1: In Cell I6, Enter the extension.
Step 2: In cell I7, Insert the Index function.
Step 3: In the pop-up dialogue box, enter the respective values for each of the arguments:
Array: $F$14:$K$23, The reference to the source data
Row_Num: To find out the row_num for the value to be found, we nested Match Function inside the Index Function:
Lookup_Value: I6, Reference of Extension.
Lookup_Array: $J$14:$J$23, reference Lookup column in source data, from where Value at I6 will be found out.
Match_type: 0, as we are finding exact match.
Column_num: 2, Position of First_name column in source data
As you must have noticed, the above scenario, can’t be performed using VLOOKUP as we found the value which was at right side of lookup value column.
Fill in your details to get our latest handbook with 200+ Fourmulas and Shortcuts
Also check out other articles on similar topics: