VLOOKUP, XLOOKUP, and SUMIFS all allow a wildcard in the Lookup_Value. But today, Chris wants wildcards wrapped around the lookup table! After trying XLOOKUP, FILTER, and COUNTIF, I finally end up with TEXTJOIN, IF, and SEARCH. Do you have a better way?
Table of Contents
(0:00) Use wildcard for the lookup range
(0:34) XLOOKUP, FILTER, COUNTIF failed
(0:54) SEARCH function on an array
(2:11) TEXTJOIN, IFERROR, and SEARCH
(3:15) Surprisingly difficult
Table of Contents
(0:00) Use wildcard for the lookup range
(0:34) XLOOKUP, FILTER, COUNTIF failed
(0:54) SEARCH function on an array
(2:11) TEXTJOIN, IFERROR, and SEARCH
(3:15) Surprisingly difficult
Transcript of the video:
This question sounded so easy, but it really threw me for a loop.
Today, Chris wrote in about a video from three years ago called Wildcards in Excel VLOOKUP.
Or SUMIFS and said, "Is there a way to do this backward? You're looking in column E for anything that contains the word apple and then looking back into column A to get something from column B".
It would probably be easier if I showed it to you.
You have a bunch of text here.
Over here, if anything contains "Apple," then it's red.
If anything contains "Cherry," then it's maroon.
You need to search the word "Gala Apple" in here and find a match for "Apple".
I have to tell you, I tried all kinds of things that I thought were going to work but wouldn't work at all.
Like the XLOOKUP, I tried to put wildcards around the LOOKUP vector.
That didn't work.
I tried to filter wildcards.
I tried to use COUNTIFS because it seemed like SUMIFS and SUMIFS works with wildcards, and none of these worked.
Here, unfortunately, this is the best that I have.
Down in the YouTube description, I'm sure that people will come up with something far easier than this.
I just worked myself into a corner.
So we're going to use the SEARCH function.
What are we going to search for?
We're going to search for all of these words, from A2 to A8, and we're going to look for them within the word, "Gala Apple".
What that does is it tells us that "Apple" is found in the sixth position.
The second one, "Cherry" is not found at all.
"Nut" is not found.
"Ice" is not found, and so on.
So we get this array of either an integer telling where it is in the SEARCH word, or the VALUE error.
The VALUE errors were causing problems down the road so I just wrapped that in the IFERROR.
Then this little thing here converts those zeros into " ", so they'll appear to the TEXTJOIN function as empty cells.
Finally, TEXTJOINing with commas in between, skipping the " "s, anything that came back from M3 here.
So putting that all together into a single formula on the inside, the SEARCH, the IFERROR, the IF, and then finally the TEXTJOIN.
It works amazingly well, without really using anything new.
The SEARCH has been around forever.
I guess TEXTJOIN, February of 2017 is the newest.
So old school formula here.
So it's looking through all of these, and "Apple" is the one to match so I'm getting red.
Also works for "Apple" and "Pineapple".
Let's see, "Cherry" is coming up as a maroon for "Cherry," "Cherry Jones," "Bing Cherry," but not "Cheri Oteri," because it's spelled wrong.
I had to add "Oteri" as an extra item in the LOOKUP table.
"Peanut, "Donut," and "Nut Roll" all managed to come back as "Brown" because of "Nut".
Then "Iceberg" is "White".
All right, "Banana Split," though.
"Banana Split" is interesting because it has two matches.
So it's found in both "Banana" and "Split," and in that case, it returns both categories, with a comma in between.
When the question came in, I'm like, "Oh, yeah, absolutely, you can do that".
I even think I replied with some formula that, of course, later I learned did not work.
This is the best I can come up with.
Even in this formula, I'm pretty sure that it is overly complicated just because I boxed myself into a corner and so many other things didn't work, you start to become desperate for something that would work.
So Chris, that's my answer.
Make sure to read the YouTube comments down below where people much smarter than me will come up with much better ways to do this.
I want to thank Chris for sending that question in, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Today, Chris wrote in about a video from three years ago called Wildcards in Excel VLOOKUP.
Or SUMIFS and said, "Is there a way to do this backward? You're looking in column E for anything that contains the word apple and then looking back into column A to get something from column B".
It would probably be easier if I showed it to you.
You have a bunch of text here.
Over here, if anything contains "Apple," then it's red.
If anything contains "Cherry," then it's maroon.
You need to search the word "Gala Apple" in here and find a match for "Apple".
I have to tell you, I tried all kinds of things that I thought were going to work but wouldn't work at all.
Like the XLOOKUP, I tried to put wildcards around the LOOKUP vector.
That didn't work.
I tried to filter wildcards.
I tried to use COUNTIFS because it seemed like SUMIFS and SUMIFS works with wildcards, and none of these worked.
Here, unfortunately, this is the best that I have.
Down in the YouTube description, I'm sure that people will come up with something far easier than this.
I just worked myself into a corner.
So we're going to use the SEARCH function.
What are we going to search for?
We're going to search for all of these words, from A2 to A8, and we're going to look for them within the word, "Gala Apple".
What that does is it tells us that "Apple" is found in the sixth position.
The second one, "Cherry" is not found at all.
"Nut" is not found.
"Ice" is not found, and so on.
So we get this array of either an integer telling where it is in the SEARCH word, or the VALUE error.
The VALUE errors were causing problems down the road so I just wrapped that in the IFERROR.
Then this little thing here converts those zeros into " ", so they'll appear to the TEXTJOIN function as empty cells.
Finally, TEXTJOINing with commas in between, skipping the " "s, anything that came back from M3 here.
So putting that all together into a single formula on the inside, the SEARCH, the IFERROR, the IF, and then finally the TEXTJOIN.
It works amazingly well, without really using anything new.
The SEARCH has been around forever.
I guess TEXTJOIN, February of 2017 is the newest.
So old school formula here.
So it's looking through all of these, and "Apple" is the one to match so I'm getting red.
Also works for "Apple" and "Pineapple".
Let's see, "Cherry" is coming up as a maroon for "Cherry," "Cherry Jones," "Bing Cherry," but not "Cheri Oteri," because it's spelled wrong.
I had to add "Oteri" as an extra item in the LOOKUP table.
"Peanut, "Donut," and "Nut Roll" all managed to come back as "Brown" because of "Nut".
Then "Iceberg" is "White".
All right, "Banana Split," though.
"Banana Split" is interesting because it has two matches.
So it's found in both "Banana" and "Split," and in that case, it returns both categories, with a comma in between.
When the question came in, I'm like, "Oh, yeah, absolutely, you can do that".
I even think I replied with some formula that, of course, later I learned did not work.
This is the best I can come up with.
Even in this formula, I'm pretty sure that it is overly complicated just because I boxed myself into a corner and so many other things didn't work, you start to become desperate for something that would work.
So Chris, that's my answer.
Make sure to read the YouTube comments down below where people much smarter than me will come up with much better ways to do this.
I want to thank Chris for sending that question in, and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
