Hello Friends its Sachin here with another blog on Index + Match Function.
The need for this blog arise when i was working on an data set where i wanted to extract a value on the basis of lookup value but i couldn’t do it with lookup function because the value which was to be extracted was situated on right side of lookup value and as we know V lookup is left to right function.
To know more about Vlookup Function Click here
Apart from the above there are certain other limitation of V lookup function which are not there in Index + Match Function which we discuss here.
Limitations of V lookup
1.) V LOOKUP can only look from left to right.
- You look a value in one column in a table, and then return a value from a column to the right.
- But what happens if you want to look from right to left? V LOOKUP simply can’t do that. INDEX/MATCH can do it
Explanation- As we have discussed in V lookup blog that it can only handle left to right lookup which means it failed here, well there one trick through which we can use to do right to left lookup through V lookup which we will discuss in another blog.
Now here let us understand that how does Index + Match work
First lets take a quick recap of Index and match function
Index Function –
The INDEX function has a very simple job – look in a column of data and return a value from the row that you specify.
To Know more about Index Function Click Here
Syntax of index Function – INDEX(Array,row number, column number)
Match Function –
he MATCH function also has a simple job – look in a column of data and return the row number of a value that you specify.
To Know More about Match Function Click Here
Syntax of Match Function -Match(lookup value , lookup array, match Type)
How does Index + Match Works
Syntax of Index + Match in case of one way lookup –
INDEX(Array, Match(Lookup value, lookup array, match type))
- Array – its a column from which we want to extract a value
- Row number – instead of directly writing number we inserted Match function to do it
- Lookup value of match function – Lookup value which we want to search in the array of match function
- Lookup array of match function – the table array from which lookup value will be searched
- Steps in which this formula work
Example – 1 Finding the name of student who scored the maximum marks
In the Max column of example we have applied Max Formula in scores
Steps In which this formula works
1st Step – Opening of match function
In the first stage the Match function gives a result of 3 because 99 number is in row 3 of lookup array
2nd step – Opening up of Index function
its the last stage in which index function gives us the value/test which is specified in row 3 of index array and we get the name “Bhavya” who scored the maximum max
2.) No Array Restriction
Another key limitation of VLOOKUP is that it requires you to specify a square table array in which your column reference cannot move beyond. It reduces the flexibility of this function when we add new column to the data
In case of Adding up of new column Vlookup function needs two changes
- Amendment in column number
- Amendment in Table array
Where as if you add an additional colon in case of index+match formula you only have to amend one thin
- Index table array
3.) Inserting new column in middle of the array
In case of V lookup if you add the new column in middle of the Lookup array the formula of V lookup will no longer work –
the reason is simple because as we insert of delete the column in middle of lookup array the lookup array will also extend or contract according to it, But the column index number which is hard coded will not change.
We can see from the below image that the new column added affected the V lookup formula but not the Index+match formula.
Example – 3
4.) Can work as an HLOOKUP
With INDEX MATCH, you can do the function of HLookup as well as Vlookup.
Example – 4 Horizontal lookup with the help of Index+Match To Find Vendor names with minimum bid.
6.) Double Lookup
Double Lookup means extracting the value on the basis of two lookup values which is not possible if use V lookup general formula
Example -5 Finding the rate on the basis of 2 lookups 1 st is Product and 2 nd is Supplier
Explanation – As i am changing the product or supplier the price is also changing. its a two way lookup formula where price is base on both the product as well as the supplier we select.
5.) INDEX MATCH is faster than VLOOKUP
The reason for this difference is actually fairly simple. VLOOKUP requires more processing power from Excel because it needs to evaluate the entire table array you’ve selected. With INDEX MATCH, Excel only has to consider the lookup column and the return column. With fewer absolute cells to consider, Excel can process this formula much faster.
Things to be taken care of:
1.) If the result of Index+match is showing #NA# error even if you can clearly see that the table contain the value specified in lookup value then it could be because of extra space after the words so in that case you can use Trim function to remove those extra spaces.
2.) Default of Match formula is 1 which means approximation to higher side so while using the Index+match function use exact match through 0 inside the Match function
To Know more about Match Function Click Here
3.) Its not a case sensitive formula so we can use small or capital alphabets whatever suits us
Thank you for reading the blog 🙂
comment below in case of any query and fell free to give your opinion about blog