Real Life Problem:
One of my friend used VLOOKUP() for his daily calculation in corporate office. Many things he can return with VLOOKUP(). One day I have asked him, "return the left side value" by using VLookup. He tried many times but failed. Finally told me to teach him Index and Match. I've done this. Now he is using Index-Match for his daily task in various MIS Reporting. Only 1 limitation I've found in VLookup() and that is, it can't return the left side (-1) column's value.
Example Data Table:
Assume that, D4:G7 is the range of players data table. D3 = "Player", E3 = "Rank", F3 = "Goal", G3 = "Country". It contains 4 rows. VLookup() can't return the players name with the help of Rank number. But Index()-Match() can return the Player Name using the same Rank number. I am just going to show you, Index-Match can do that, what a VLookuo() can't. My friend used the formula in E11 Cell:
And typed 3 in D10 Cell. It returns #VALUE error.
Solution:
In I17 cell I've used this one:
It returns "Balak" the correct answer.
VLookup(), Index()-Match() both supports array formula. Although Vlookup() is popular but it has a limitation and it is true. I personally like Index and Match and believe that, it is powerful.
One of my friend used VLOOKUP() for his daily calculation in corporate office. Many things he can return with VLOOKUP(). One day I have asked him, "return the left side value" by using VLookup. He tried many times but failed. Finally told me to teach him Index and Match. I've done this. Now he is using Index-Match for his daily task in various MIS Reporting. Only 1 limitation I've found in VLookup() and that is, it can't return the left side (-1) column's value.
Example Data Table:
Assume that, D4:G7 is the range of players data table. D3 = "Player", E3 = "Rank", F3 = "Goal", G3 = "Country". It contains 4 rows. VLookup() can't return the players name with the help of Rank number. But Index()-Match() can return the Player Name using the same Rank number. I am just going to show you, Index-Match can do that, what a VLookuo() can't. My friend used the formula in E11 Cell:
=VLOOKUP(D10,E4:G7,-1,FALSE)
And typed 3 in D10 Cell. It returns #VALUE error.
Image 1: Vlookup() returns #Value error while returning the left side value
Solution:
In I17 cell I've used this one:
=INDEX(D4:D7,MATCH(D10,E4:E7,0))
It returns "Balak" the correct answer.
Image 2: Index-Match() works fine
VLookup(), Index()-Match() both supports array formula. Although Vlookup() is popular but it has a limitation and it is true. I personally like Index and Match and believe that, it is powerful.
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw