Shop at: Holiday Gifts
Thank you for supporting MrExcel during my 18 years in business. Today, three very unique limited edition products and a tip on using INDEX/MATCH instead of VLOOKUP.
Thank you for supporting MrExcel during my 18 years in business. Today, three very unique limited edition products and a tip on using INDEX/MATCH instead of VLOOKUP.
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2061.5: Small Business Saturday Well, it was 18 years ago today, on the Friday after Thanksgiving, when I wrote my first tip, Tip 001.html and launched MrExcel.com.
And for those of you who followed my videos or used the help site or buy my books, I want to thank you for keeping me going for 18 years.
It has been my full-time gig for 16 of those 18 years.
So whether you’ve seen me in a seminar or watched my videos, I want to thank you for supporting us.
We are definitely a small business, and so participate in Small Business Saturday.
It's a Small Business Saturday shop local, you can find those unique gifts that you aren't kind of find at the big-box retailers.
And I know most people buy their books from Amazon today, but I have three very unique items.
What I have here is the MrExcel XL book, the 40 greatest Excel tips of all time with a unique handmade cover.
This is from Hatch Show Print.
It has been in Nashville for over a hundred years making country music posters.
These are handmade, they created a hundred of these posters for me and then we bound them in the book.
Very limited edition.
And then, I also have another letterpress poster.
This one from Marietta, Ohio, Bobby Rosenstock at JustAJar Design.
Hand-rolled on his Vandercook press, 100 of these made.
Talks about how INDEX & MATCH can do what VLOOKUP can't do - can't go to the left.
And then finally, a small little gift here, a set of playing cards.
You can learn Excel when playing poker, gin or go fish.
All of the cards, ace through ten, have a tip on them, an Excel tip on them, alright?
So, great gifts for the Excel guru in your life or for you if you want to pass this on to someone else.
Don't type this link in; it’s right down there in the YouTube description.
Just go to the YouTube description and click through.
Alright, let me give you a tip today and it's talking about how VLOOKUP cannot go to the left.
So here we have a lookup table with Product in G, and Price to the left of that.
Well, VLOOKUP can't do that.
VLOOKUP can't say, ‘hey, we're going to the -1 column’.
That's against the rule.
So what we have to do, we have to do is say, =INDEX and first we point to the Prices.
So the Prices are over here in F. Press F4, comma and in which of those prices we want, we want the answer from the MATCH.
MATCH is going to tell us what row number, P101 is in this list of products over here, and for comma 0 for an exact match which is like the False in VLOOKUP.
And finally, I multiply that by the Quantity over in A2, alright.
So, it does the lookup and multiplies it and it works.
Alright now, hey, the poster is talking about this particular advantage: INDEX & MATCH can go left but there's a lot of other great advantages.
If I'm doing a seminar for 100 people, there will be about 2 people in that room who use INDEX & MATCH instead of VLOOKUP.
Alright, so lots of great little gifts there.
Again, thank you for supporting MrExcel over these years.
We'll see you next time for another netcast from MrExcel.
And for those of you who followed my videos or used the help site or buy my books, I want to thank you for keeping me going for 18 years.
It has been my full-time gig for 16 of those 18 years.
So whether you’ve seen me in a seminar or watched my videos, I want to thank you for supporting us.
We are definitely a small business, and so participate in Small Business Saturday.
It's a Small Business Saturday shop local, you can find those unique gifts that you aren't kind of find at the big-box retailers.
And I know most people buy their books from Amazon today, but I have three very unique items.
What I have here is the MrExcel XL book, the 40 greatest Excel tips of all time with a unique handmade cover.
This is from Hatch Show Print.
It has been in Nashville for over a hundred years making country music posters.
These are handmade, they created a hundred of these posters for me and then we bound them in the book.
Very limited edition.
And then, I also have another letterpress poster.
This one from Marietta, Ohio, Bobby Rosenstock at JustAJar Design.
Hand-rolled on his Vandercook press, 100 of these made.
Talks about how INDEX & MATCH can do what VLOOKUP can't do - can't go to the left.
And then finally, a small little gift here, a set of playing cards.
You can learn Excel when playing poker, gin or go fish.
All of the cards, ace through ten, have a tip on them, an Excel tip on them, alright?
So, great gifts for the Excel guru in your life or for you if you want to pass this on to someone else.
Don't type this link in; it’s right down there in the YouTube description.
Just go to the YouTube description and click through.
Alright, let me give you a tip today and it's talking about how VLOOKUP cannot go to the left.
So here we have a lookup table with Product in G, and Price to the left of that.
Well, VLOOKUP can't do that.
VLOOKUP can't say, ‘hey, we're going to the -1 column’.
That's against the rule.
So what we have to do, we have to do is say, =INDEX and first we point to the Prices.
So the Prices are over here in F. Press F4, comma and in which of those prices we want, we want the answer from the MATCH.
MATCH is going to tell us what row number, P101 is in this list of products over here, and for comma 0 for an exact match which is like the False in VLOOKUP.
And finally, I multiply that by the Quantity over in A2, alright.
So, it does the lookup and multiplies it and it works.
Alright now, hey, the poster is talking about this particular advantage: INDEX & MATCH can go left but there's a lot of other great advantages.
If I'm doing a seminar for 100 people, there will be about 2 people in that room who use INDEX & MATCH instead of VLOOKUP.
Alright, so lots of great little gifts there.
Again, thank you for supporting MrExcel over these years.
We'll see you next time for another netcast from MrExcel.
