A question from my Minneapolis seminar: How can you do a VLOOKUP for Apple when then matching text has characters both before and after Apple? It turns out that VLOOKUP can accept a wildcard or a pair of wild cards and this is easier than you would think. The same concept can be used with SUMIFS to find the total of all records that contain Apple anywhere in the text.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2277. Wildcards in Excel VLOOKUP and SUMIFS.
Hey welcome back to MrExcel netcast. I’m Bill Jelen. Well today, I was in Minneapolis, Minnesota doing a seminar and a great question came up. They said, you know, look, I have to look for these words.
They were—I think they were actually check numbers or something like that.
But the field that I’m looking at has some text or numbers before the word and different text and numbers after the word.
So I need to find, you know like, if fig is anywhere in there, I need to find the matching number and I said, oh yeah, you know, this is not well known but VLOOKUP will allow you to do wild cards.
Alright, and in this particular case because there’s stuff both before and after Apple, we have to put an asterisk, an ampersand, Apple and then another ampersand and an asterisk in quotes like that. Alright.
So we’re looking for anything Apple, anything and we’re going to look for it over here in this lookup table.
Press that 4 comma 2 comma false like that and double click to copy it down and it’s finding the match, right.
Now, someone in the audience, Tim, said, “Well hey you’re going to have a problem if you have both Apple and Crab Apple,” and that’s true you will but you know… So I guess it’s not perfect.
And then you know I love how these questions evolve, right. So then I show this and someone said, “Oh, oh wait.
Would that work with sum IFS?” Alright, so I created a copy of the data set and this time have multiple items with apple.
Alright, so we’re looking for all of the words that have an Apple anywhere in the middle and we want to sum those, alright.
So total so equal sum ifs, sum ifs and the sum range of course is those numbers over there.
Press f4 comma and then the pairs of criteria we want to look through all of these codes here to see if they’re equal to asterisk, ampersand, apple, ampersand, asterisk like that. Alright.
So out of the corresponding numbers from F if column E contains the word apple or banana or cherry or whatever alright.
So we’ll double click to enter that or enter that and double click to copy that down.
And I went and I found all the apples and brought there here to the top so if we choose these three cells here, 2 6 6 4 and we get 2 6 6 4 so it is working. Alright.
So a couple of cool little tricks there using wildcard before and after what we’re looking for in order to find all of the things in the data base that match apple.
Hey if you like what you see in these videos, don’t forget to click subscribe and then ring that bell.
If you have any questions or comments about how you use wildcards or other great ways that work, leave a comment down below. I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Hey welcome back to MrExcel netcast. I’m Bill Jelen. Well today, I was in Minneapolis, Minnesota doing a seminar and a great question came up. They said, you know, look, I have to look for these words.
They were—I think they were actually check numbers or something like that.
But the field that I’m looking at has some text or numbers before the word and different text and numbers after the word.
So I need to find, you know like, if fig is anywhere in there, I need to find the matching number and I said, oh yeah, you know, this is not well known but VLOOKUP will allow you to do wild cards.
Alright, and in this particular case because there’s stuff both before and after Apple, we have to put an asterisk, an ampersand, Apple and then another ampersand and an asterisk in quotes like that. Alright.
So we’re looking for anything Apple, anything and we’re going to look for it over here in this lookup table.
Press that 4 comma 2 comma false like that and double click to copy it down and it’s finding the match, right.
Now, someone in the audience, Tim, said, “Well hey you’re going to have a problem if you have both Apple and Crab Apple,” and that’s true you will but you know… So I guess it’s not perfect.
And then you know I love how these questions evolve, right. So then I show this and someone said, “Oh, oh wait.
Would that work with sum IFS?” Alright, so I created a copy of the data set and this time have multiple items with apple.
Alright, so we’re looking for all of the words that have an Apple anywhere in the middle and we want to sum those, alright.
So total so equal sum ifs, sum ifs and the sum range of course is those numbers over there.
Press f4 comma and then the pairs of criteria we want to look through all of these codes here to see if they’re equal to asterisk, ampersand, apple, ampersand, asterisk like that. Alright.
So out of the corresponding numbers from F if column E contains the word apple or banana or cherry or whatever alright.
So we’ll double click to enter that or enter that and double click to copy that down.
And I went and I found all the apples and brought there here to the top so if we choose these three cells here, 2 6 6 4 and we get 2 6 6 4 so it is working. Alright.
So a couple of cool little tricks there using wildcard before and after what we’re looking for in order to find all of the things in the data base that match apple.
Hey if you like what you see in these videos, don’t forget to click subscribe and then ring that bell.
If you have any questions or comments about how you use wildcards or other great ways that work, leave a comment down below. I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
