First thing you need to understand about index and match function is that index and match are not one/ joint function. They are two separate lookup functions of excel.
Why we need it, ha? You see, there are certain limitation of VLOOKUP and HLOOKUP. Index match can do the things which VLOOKUP cannot do.
- 1 Why Index Match is better than VLOOKUP?
- 2 Benefits of using Index and Match function:
- 3 How does match function works in excel?
- 3.1 Syntax of Match function
- 3.2 Example 1: Locating the position of cell in vertical array using match.
- 3.3 Example 2: Locating the position of cell in horizontal array using match.
- 3.4 Example 3: Practical use of locating the position of cell using less than match type:
- 3.5 Example 4: Practical use of locating the position of cell using more than match type:
- 4 How does index function works in excel?
- 5 How do I do Index Match?
Why Index Match is better than VLOOKUP?
VLOOKUP is one of the most useful lookup function of excel. You already had mastered the same… Not yet…?? (Click here to have idea of Vlookup).
Let’s give a try… Before understanding how Index Match works, there’s small exercise for you all to know why you need to understand Index Match functions?
This is the data available with you and you need to find out employee name based on the employee ID. Simple, you can easily solve it through VLOOKUP. I request you to just try to resolve this through VLOOKUP. (Pic1)
Done…??? Oh no… Stuck on the argument col_index_num…?? Probably Yes…
Wondering why the same didn’t worked…?? To appreciate the same, we need to go to the basic understanding of the arguments of the formula.
The VLOOKUP formula consists of 4 arguments;
- Lookup_value: This is the argument where the user is required to enter the value or cell reference which will work as correspondence for getting the corresponding value.
- Table_array: The table array is the cell reference of the table from in which the correspondence and corresponding value both exists.
- Col_index_num: This is the index number of column of the table from which the corresponding value is to be sought.
- Range_lookup: This is a condition of having approximate or exact match of correspondence in table. (Click here to know more about range lookup and its practical use)
So here is the checklist why one should use Index and Match instead of VLOOKUP and HLOOKUP:
- It is considerable that, in Vlookup formula, the excel software by default searches the corresponding value in the initial column of the table selected in table array.
- Moreover, it works only one way i.e. from left to right. It can fetch corresponding value from right column. However, it fails when the correspondence is not in the initial column of the table_array or the column of the corresponding value is on the left side of the column having lookup_value.
- VLOOKUP can have approximate match for less than range lookup.
- In Vlookup formula the table needs to be together and shall consist both column of correspondence and corresponding value. There’s no such requirement in Index and Match formula. Both columns can be in two different tables, two different sheets and even files are permitted.
Benefits of using Index and Match function:
- No need to remember column number while writing formula.
- It can look up from left to right too.
- No need to have lookup value in initial column of table array.
- No need to remember when to use VLOOKUP and when to use HLOOKUP.
- Match type can be less than / exact / more than.
So, let’s Explore topic for the day – ‘Index and Match’
How does match function works in excel?
Match function returns the relative position of the lookup value in the array.
Syntax of Match function
Match ( lookup_value, lookup array, [match_type])
Note: Arguments given in square bracket shows that it is optional and if one does not give any value in optional argument, excel takes default value of the optional argument.
Let’s understand each of the argument of match function one by one.
- Lookup value – It is the value you want to look up in the lookup array.
- Lookup Array – It is the range of data into which you want to locate the relative position of lookup value. Lookup array can either be horizontal or vertical.
- [Match Type] – It is the type of match you want to have in match function.
1 – Less than Match (To perform less than match, data must be sorted in ascending order.) However, it considers equal to or less than match type.
0 – Exact Match.
-1 – More than Match (To perform more than match, data must be sorted in descending order.) However, it considers equal to or more than match type.
Let’s take 4 examples to get clarity on How does match function work in excel?
Example 1: Locating the position of cell in vertical array using match.
Here is the employee master containing Employee name, their birth date, location of work and unique employee ID assigned to them. (Pic2)
Now let’s check out relative position of “Mehul Pathak” in Employee name column. (Pic3)
It means “Mehul Pathak is relatively located in 3rd cell of array.
F3- Lookup value
A3:A12 – Vertical array
0 – For exact match
Instead of taking lookup value from another cell i.e. F3, you can write “Mehul Pathak” (In double inverted comma)
I prefer and recommend taking lookup value from another cell, as you can easily change base cell i.e. F3 rather than changing formula.
Example 2: Locating the position of cell in horizontal array using match.
Let’s check out relative position of Employee ID in the headings using match. (Pic4)
It means “Employee ID” is relatively located in 4th cell of array.
F4- Lookup value
A2:D2 – Horizontal array
0 – For exact match
Example 3: Practical use of locating the position of cell using less than match type:
Let’s say we want to find that how many employees joined firm before particular date to give promotion to older employees. Let’s say employees who joined on or before 31.03.1991 will be eligible for promotion. (Pic5)
It means 2 employees joined firm on or before 31.03.1991
F3- Lookup value.
B3:B12 – Vertical array
0 – For Less than match type. (However, it considers equal to or less than match type and data must be sorted in ascending order.)
Example 4: Practical use of locating the position of cell using more than match type:
Let’s say we want to find that how many employees joined firm after particular date to give raise in salary. Let’s say employees who joined on or after 01.04.1991 will be eligible for raise.
But, to perform more than match type, we need to sort the data in descending order of date. i.e. Newest to Oldest.
Select the table and press Ctrl + Shift + L (Shortcut to add filter in selected cells) to add filter and sort the joining date column by newest to oldest. (Pic6)
It means 8 employees joined firm on or after 01.04.1991
F3- Lookup value.
B3:B12 – Vertical array
0 – For More than match type. (However, it considers equal to or more than match type and data must be sorted in descending order.)
Note: One can perform any match type in the horizontal array too.
So, we know how match function works. Let’s get to know about its brother index function..
How does index function works in excel?
Index function basically returns the value or reference of the cell located at the intersection of column and row from a given range of data.
Syntax of index function:
Index (array, row_num, [Column_num])
- Array – It is the array from which one wants to extract cell value based on input of row number and column number.
- Row_Num – It is the row of the array from which one wants to extract data.
- Column_Num – It is the column of array from which one wants to extract data.
Let’s take three examples to get more clarity about how does index function work in excel?
Example 1: Extracting data from array by row number using index:
Now let’s check out who is located on 6th row of employee master using index function. (Pic7)
=Index(Array, row num, [column num]
A3:A12 – It is the array (range of data) for the lookup.
6 – We want to locate 6th cell of array. So, write 6 to lookup 6th row of array using index function.
Column num – As we only want to look up for row, don’t write anything or just write 1.
Output: “Gautam Mishra” (6th row of array)
If you want to look up for 5th row, write
Output: “Ravi Jadeja”
A3:A12 – Vertical Array
6 – Row Number
Example 2: Extracting data from array using column number:
Now let’s just check out what is the 3rd heading in the heading list using index function. (Pic8)
Here is one interesting thing about index function.
When you select the array horizontally, you can mention column number in the 2nd argument of row number itself.
Output = “Location”
A2:D2 – Vertical Array
3 – Column Number
Or else, you can write with row number too.
A2:D2 – Horizontal Array
1 – Row Number
3 – Column Number
If you want to look up for 2nd column of the same array, write:
Output: “Birth Date”
Example 3: Extracting data from array by intersection of row and column number using index function:
Here, when we want to use both row number and column number, we should select data which requires both column and row number as an input.
Let’s say, we want to checkout location of Ravi Jadeja.
Here, we will write 5 in row number to get Ravi Jadeja and 3 as a column number to get his location. (Pic9)
A3:D12 – Array
5 – Row number (to get Ravi Jadeja)
3 – Column number (to get location of Ravi Jadeja – Delhi)
How do I do Index Match?
Let’s put Index match together. Using them together can-do miracles in excel.
So, here is step by step guide to using Index Match function.
Example 1: Looking up data into array by two variables using Index Match function:
Let’s say one wants to have Employee ID of Sunil Sharma by index match function. We have got following data. (Pic10)
To use index function, we need two inputs. One is row number (In our case it would be row number of Sunil Sharma in array) and another is column number (In our case it would be column number of Employee ID in array)
Both input of index function can be obtained by match function.
Let’s use match function to get relative position of Sunil Sharma in employee list. (Pic11)
Now we will find relative position of Employee ID in headings using match. (Pic12)
Now we have both the inputs needed to use index function.
Write the formula of index function using those two outputs of match function. (Pic13)
A3:D12 – Array
G4 – Row number
G5 – Column Number
“Excellent, but it uses 3 cells of excel, I want to merge it in one cell only!”
“Okay! Let’s go for it!”
Example 2: Looking up data into array by two variables using Index Match function in one cell only:
Write the array of index function – A3:D12 (Pic14)
Here, we should provide row number as an input to index function. Hmm!! We can use Match function here!! The same match function used in above example for finding relative position of Sunil Sharma. (Pic15)
For column number, use match function to find relative position of employee ID in headings. (Pic16)
Just hit the enter.
Output: E007 (We got the employee Code) 🙂
You have got all the basics of index match function. So, grab an opportunity to use it at your workplace / home.
Here is the Practice File on How Do I Do Index and Match?
Practice File : How Do I Do Index and Match?