Tom asks how to do a VLOOKUP with a wildcard. This interesting question has many possible approaches to solve the problem and it will become the June/July challenge of the month. I encourage you to enter our contest to win cool prizes. Watch Episode 784 to see how!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the Mr.Excel Netcast. I'm Bill Jelen.
Today we have a question send in by Tom and it's such an interesting question with so many different approaches to solve it.
I'm making it the challenge of the month.
So I'm inviting you to play along and send in your best answers to this.
Will give away some live lessons DVDs and also have some podcast DVDs, but it initially seems like a VLOOKUP question.
We have a bunch of phrases over here in column A and you'll notice that every phrase has a color.
So the ocean is blue or the ocean is red or red is my favorite color.
The color can appear at the end it can appear at the beginning or in the middle.
I like his yellow shirt and basically what tom needs to do is, over here on the right hand side he has a table of all the colors, and who that is assigned to.
So if it has the word blue it's Joe's account and basically we're looking for either a formula or a macro or a user-defined function that will allow us to do, let's call it a wild card VLOOKUP.
I mean VLOOKUP needs to find the exact answer.
So if we have the word blue here without anything else in the phrase it would be easy. It's just a VLOOKUP.
Now I'm going to make this a little bit more difficult and say that the key word table can have as many entries as we want so nesting IF statements is not a valid way to go.
I want something else either an array formula, or a macro and so come here to Mr.Excel.com and the left hand navigation Choose challenge of the month and you'll see the whole explanation you can download the file, and then there's a link there where you can post your entries.
We'll keep this open till about July 15th.
Invite you to send in your entry.
It'll be interesting to see how many different approaches we have to this.
I know the last challenge of the month.
There were 380 different formulas that were sent in.
Just interesting to see all the different approaches that people could take.
So there you have it. New challenge of the month and want you to give it a try.
Thanks for stopping by we'll see you next time for another netcast from MrExcel.
Today we have a question send in by Tom and it's such an interesting question with so many different approaches to solve it.
I'm making it the challenge of the month.
So I'm inviting you to play along and send in your best answers to this.
Will give away some live lessons DVDs and also have some podcast DVDs, but it initially seems like a VLOOKUP question.
We have a bunch of phrases over here in column A and you'll notice that every phrase has a color.
So the ocean is blue or the ocean is red or red is my favorite color.
The color can appear at the end it can appear at the beginning or in the middle.
I like his yellow shirt and basically what tom needs to do is, over here on the right hand side he has a table of all the colors, and who that is assigned to.
So if it has the word blue it's Joe's account and basically we're looking for either a formula or a macro or a user-defined function that will allow us to do, let's call it a wild card VLOOKUP.
I mean VLOOKUP needs to find the exact answer.
So if we have the word blue here without anything else in the phrase it would be easy. It's just a VLOOKUP.
Now I'm going to make this a little bit more difficult and say that the key word table can have as many entries as we want so nesting IF statements is not a valid way to go.
I want something else either an array formula, or a macro and so come here to Mr.Excel.com and the left hand navigation Choose challenge of the month and you'll see the whole explanation you can download the file, and then there's a link there where you can post your entries.
We'll keep this open till about July 15th.
Invite you to send in your entry.
It'll be interesting to see how many different approaches we have to this.
I know the last challenge of the month.
There were 380 different formulas that were sent in.
Just interesting to see all the different approaches that people could take.
So there you have it. New challenge of the month and want you to give it a try.
Thanks for stopping by we'll see you next time for another netcast from MrExcel.
