Index Function in Excel Definition with Formula:
The index function in excel returns a value from the table(array). We obtain the value from the intersection of the row number and the column number defined in the formula.
The row_num and column_num are according to the array that we choose.
Formula: =INDEX(array, row_num, [column_num])
Excel Match Function Definition with Formula:
Excel Match function returns the position of the lookup value in the array. The answer obtained is the row number of the lookup value.
Formula: =MATCH(lookup_value, lookup_array, [match type])
lookup_value: To find the position of this value. (row number)
lookup_array: Data Range or Table into which we have to lookup for the value.
match_type: 0, 1, -1
0 finds the exact value.
1 finds the highest value that is less than or equal (<=) to lookup_value. Values in the array must be in ascending order.
-1 finds the smallest value that is greater than or equal (>=) to lookup_value.
Values in the array must be in descending order. The values can be sorted using the Filter in excel.
Explanation with Example Questions:
Brenda is very busy today as her friend, Roselle, visited her. Roselle is having problems with her job. She works at a gym maintaining the client’s records and knows Brenda has a good knowledge of excel. She asks Brenda to teach her the concept of VLOOKUP and hlookup as someone at work told her to learn them.
But before teaching these concepts, Brenda explains the index function in excel. She prepares some questions for her friend to teach her. Brenda also tries to teach her the excel match function with these questions.
- Check the membership period of Steve.
- Check if Emma paid her fees or not.
- Check Emily’s position in the database.
- Check who has a membership period of 9 months using the index match formula.
Now coming to the answers:
Check the membership period of Steve.
This problem can be solved using the index function in excel.
Formula: =INDEX(E5:F12,1,2)
Answer: 1
By solving the problem, we get that the membership period of Steve is one month.
In this solution formula,
Array – E5:F12
Row_num – 1
Column_num – 2
** In this problem, if we chose an array of F5:F12 instead of E5:F12, our column_num would have been “1” instead of 2.
Check if Emma paid her fees or not.
This problem is also solved using the index function in excel.
Formula: =INDEX(G5:G12,5,1)
Answer: Y
By solving the problem, we get that Emma paid her fee.
In this solution formula,
Array – G5:G12
Row_num – 5
Column_num – 1
** In this problem, if we chose an array of E5:G12 instead of G5:G12, our column_num would have been “3” instead of 1.
Check Emily’s position in the database.
This problem is solved using the excel match function.
Formula: =MATCH(E12,E5:E12,0)
Answer: 8
By solving the problem, we get that Emily’s record is in the 8th position in the database. We get the row number of Emily as the solution.
In this solution formula,
Array – E5:E12
Lookup_value – E12
Match Type – 0 (exact match)
Check who has a membership period of 9 months.
This problem is solved using the index match formula in excel. The index and match in excel work the same as the vlookup function.
Formula: =INDEX(E5:F12,MATCH(F12,F5:F12,0),1)
Answer: Emily
By solving the problem, we get Emily to have a 9-month membership.
In this solution formula,
Array – E5:E12
Column_num – 1
Row_num – 8
Row_num obtains by solving the excel match function MATCH(F12, F5:F12,0). Here, F12(“9”) is the lookup_value; F5:F12 is the array and 0 is for exact match.
Roselle is happy as she understands the concept of index function in excel clearly. She seems proud of her friend, Brenda.
After this, both go to the balcony to enjoy the tea and muffins together and remember the old times.