This function is a lifesaver. I use some really large spreadsheets in accounting and vlookups can be a pain in the behind especially when you have deadlines to meet. Thanks for your very clear explanation!
Thanks. The live example really helped make sense of the formulea. Just so you know, A title like "Stop using Vlookup, here's why" would have beem more engaging for this video.
Hi, can anybody help me on how to make the filter that stand alone like the one in the video? Like the one in french cell, can change it to another country by click in it 🙏
Make sure you are on the cell in which you want to create a drop down, now go to data and then data validation. Click on list from drop down menu and then select the whole array in which you wish to get value from.
Your explaining vlookup and index matching, but you deleted those 4 cells in a square individually ? with your mouse ? Lol but seriously great explanation
I'll take a whack. Index and Match are both "looking" at their own specific columns as ranges in this example. So when the new column is added, excel knows to update the "C2:C8" in index() to D2:D8 and the lookup still functions the same. With vlookup, the range in the 2nd argument is updated (it goes from A2:C8 to A2:D8) BUT the 3rd argument is just a simple integer. Excel doesn't know that it would need to be updated so it's left as a 3 and the vlookup gives the blank value in column C as its return unless/until the column argument is update to 4 or the data is put back into column C. Index/match can look either direction because they're both looking at single columns. Index takes ([array],[row],[column]) and returns whatever value is in that row and column of the array it's passed. In this case, it's passed a 1 dimensional columnar array and no argument for column is needed (it assumes column coordinate 1 in that case). And then the Match function takes ([value],[array],[type]) as its arguments and returns the coordinate where the value exists in the larger array it's passed. That value is piped into the row argument of index in this example. So Match looks for the value of cell F2 in the Range A2:A8, finds it A5 (the 4th element of the array) and then it passes that value (4) to the row argument of Index, telling index to spit out the value of the 4th row (and implicitly 1st column) of range C2:C8 which is in C5 in the worksheet. To make an index/match call equivalent to the vlookup, it would be: =INDEX(A2:C8,MATCH(F2,A2:A8,0),3) And would have the same flaw as the vlookup since it's now relying on that "3". However if it is rebuilt as =INDEX(A2:C8,MATCH(F2,A2:A8,0),MATCH(A1:C1,"Population",0)) it would be able to keep functioning because excel would update the A2:C8, A2:A8, and A1:C1 to reflect any insertions, and the values provided to index by match would still point to the desired data. One of my favorite use cases is using vector math with arrays to build a multiple criteria lookup. They look a little weird like: =INDEX([outputs range], MATCH(1,([lookup range 1]=[value1])*([lookup range 2]=[value2])*(etc.),0),1) or more realistically =INDEX(D2:D100,MATCH(1,(A2:A100=F2)*(B2:B100=F3)*(C2:C100=F4),0),1) This would find the cell(s) in column D where F2 is in A, F3 is in B, and F4 is in C (and only those rows where all 3 match). And there are ways if multiple hits exist to further refine them to get at specific instances. It gets REALLY powerful. And he doesn't even get into how much better index/match perform in terms of system resources. vlookup/hlookup on big data sets get painful way sooner than index/match.
IMHO the best selling point on adopting the apparent complexity of index/match (other than the much more flexible usage) is the significantly better resource usage than vlookup/hlookup.
I enjoy how you break down and simplify the concepts in your videos. Thank you!
My pleasure!
You make it so, simple sir. I was wandering on the internet for the simple way to use it on large file . Very useful . Thanks again.
This function is a lifesaver. I use some really large spreadsheets in accounting and vlookups can be a pain in the behind especially when you have deadlines to meet. Thanks for your very clear explanation!
Great to hear! Thank you!
nice. Calm voice and calm presentation...
Thank you 🙂
Thanks. The live example really helped make sense of the formulea.
Just so you know, A title like "Stop using Vlookup, here's why" would have beem more engaging for this video.
Thank you.
Most easier guide ever seen! Good job and appreciate your sharing!
Wilson, Thank you. I appreciate it.
thank you! I finally understood why this approach is superior to vlookup :)
Thank you for the video. I feel so powerful now I have acquired this knowledge
You are very welcome
Thanks for this video Chris - super informative and it helped me to build the report I needed. Great job!
Very welcome!
"vlookuo only go to the right."
Thanks sir i'm sold
You should see the new XLOOKUP function. It is amazing!
@@ChrisMenardTraining i will, im going there
Very helpful - thanks! I'll use this today :) If the dataset was a Table, and a column was inserted, wouldn't the vLookUp adjust?
Thanks buddy, it was really helpful.
You're welcome!
Thank you for this video! Keep up the good work!
You bet!
I am loving your videos. Congratulation. Great way to keep up to date on excel.
Thank you!
I'm here wanting to know the difference of the 2 functions. And 3:03 and 5:26 answer my question. Thank you!
Definitely the right game play here.
Thanks!
Welcome!
or you can use tables and use the column function and column name
Excellent
Thank you so much 😀
Thanks man great!
Glad it helped!
Hi, can anybody help me on how to make the filter that stand alone like the one in the video?
Like the one in french cell, can change it to another country by click in it 🙏
Make sure you are on the cell in which you want to create a drop down, now go to data and then data validation. Click on list from drop down menu and then select the whole array in which you wish to get value from.
@@devangpatel2564❤
Your explaining vlookup and index matching, but you deleted those 4 cells in a square individually ? with your mouse ? Lol but seriously great explanation
Good explanation, but you could explain more WHY/HOW index/match can look from right to left when columns are inserted.
I'll take a whack. Index and Match are both "looking" at their own specific columns as ranges in this example. So when the new column is added, excel knows to update the "C2:C8" in index() to D2:D8 and the lookup still functions the same. With vlookup, the range in the 2nd argument is updated (it goes from A2:C8 to A2:D8) BUT the 3rd argument is just a simple integer. Excel doesn't know that it would need to be updated so it's left as a 3 and the vlookup gives the blank value in column C as its return unless/until the column argument is update to 4 or the data is put back into column C.
Index/match can look either direction because they're both looking at single columns. Index takes ([array],[row],[column]) and returns whatever value is in that row and column of the array it's passed. In this case, it's passed a 1 dimensional columnar array and no argument for column is needed (it assumes column coordinate 1 in that case). And then the Match function takes ([value],[array],[type]) as its arguments and returns the coordinate where the value exists in the larger array it's passed. That value is piped into the row argument of index in this example. So Match looks for the value of cell F2 in the Range A2:A8, finds it A5 (the 4th element of the array) and then it passes that value (4) to the row argument of Index, telling index to spit out the value of the 4th row (and implicitly 1st column) of range C2:C8 which is in C5 in the worksheet. To make an index/match call equivalent to the vlookup, it would be:
=INDEX(A2:C8,MATCH(F2,A2:A8,0),3)
And would have the same flaw as the vlookup since it's now relying on that "3". However if it is rebuilt as
=INDEX(A2:C8,MATCH(F2,A2:A8,0),MATCH(A1:C1,"Population",0))
it would be able to keep functioning because excel would update the A2:C8, A2:A8, and A1:C1 to reflect any insertions, and the values provided to index by match would still point to the desired data.
One of my favorite use cases is using vector math with arrays to build a multiple criteria lookup. They look a little weird like:
=INDEX([outputs range], MATCH(1,([lookup range 1]=[value1])*([lookup range 2]=[value2])*(etc.),0),1)
or more realistically
=INDEX(D2:D100,MATCH(1,(A2:A100=F2)*(B2:B100=F3)*(C2:C100=F4),0),1)
This would find the cell(s) in column D where F2 is in A, F3 is in B, and F4 is in C (and only those rows where all 3 match). And there are ways if multiple hits exist to further refine them to get at specific instances. It gets REALLY powerful. And he doesn't even get into how much better index/match perform in terms of system resources. vlookup/hlookup on big data sets get painful way sooner than index/match.
IMHO the best selling point on adopting the apparent complexity of index/match (other than the much more flexible usage) is the significantly better resource usage than vlookup/hlookup.