Mohammad asks: How do we vlookup part of a cell in part of another cell. For example, find "bank of america" in "you have received money from bank of america account". This video shows how to solve this with VLOOKUP, XLOOKUP, and SUMIFS.
Topics here:
wildcard excel lookup
wildcards in excel formula
how to use excel wildcards
excel how to search wildcards
what are excel wildcards
wildcards excel search
excel wildcard search in formula
excel wildcard search not working
excel wildcards search
microsoft excel wildcards
microsoft excel wildcard in formula
wildcard not working excel
excel wildcards does not work
excel using wildcards
excel wildcard for text
excel wildcards vlookup
excel wildcard xlookup
excel wildcard zero or more characters
excel wildcard text lookup
excel wildcard text comparison
excel wildcard use
excel using wildcards in vlookup
excel using wildcards in sumifs
excel vlookup wildcard match
excel vlookup wildcard search
excel vlookup wildcard not working
excel sverweis wildcard
Table of Contents
(0:00) Wildcard in VLOOKUP
(1:01) Wildcard in XLOOKUP
(1:57) Buy a book
(2:07) Wildcard in SUMIFS
Topics here:
wildcard excel lookup
wildcards in excel formula
how to use excel wildcards
excel how to search wildcards
what are excel wildcards
wildcards excel search
excel wildcard search in formula
excel wildcard search not working
excel wildcards search
microsoft excel wildcards
microsoft excel wildcard in formula
wildcard not working excel
excel wildcards does not work
excel using wildcards
excel wildcard for text
excel wildcards vlookup
excel wildcard xlookup
excel wildcard zero or more characters
excel wildcard text lookup
excel wildcard text comparison
excel wildcard use
excel using wildcards in vlookup
excel using wildcards in sumifs
excel vlookup wildcard match
excel vlookup wildcard search
excel vlookup wildcard not working
excel sverweis wildcard
Table of Contents
(0:00) Wildcard in VLOOKUP
(1:01) Wildcard in XLOOKUP
(1:57) Buy a book
(2:07) Wildcard in SUMIFS
Transcript of the video:
Wildcards and VLOOKUP, XLOOKUP, and SUMIFS.
Today's question from Mohammad on YouTube.
He was trying to look up the words “Bank of America” in text that says, "You have received money from the Bank of America account,".
Mohammad even had the asterisk here with the ampersands.
That's the whole trick right there. So here I have a list of banks here.
I have a list of text that has those banks in the middle of the text, and I want to get these amounts. So with VLOOKUP, the old VLOOKUP, it just works.
What we're looking up instead of A3 is quote, asterisks, quote, ampersand, A3, ampersand, quote asterisks, quote.
And then just make sure to put comma False at the end so we're looking for an exact match, an exact wild card match.
All right, and it works. Why does this one not work?
Because over here it says we got it from Mellon Bank and here it's BNY Mellon Bank.
The other reason that this would not work is if we had Character 160 spaces over here, like if this data came from the web, which it feels like it came from the web, then that would not work.
The place where it really becomes difficult is when we switch over to the new XLOOKUP.
VLOOKUP handled wild cards by default.
So that's asterisk for any number of characters, a question mark for a single character. There's also a special handling for the tilde.
If you're actually looking for an asterisk, you would have to put tilde asterisk, but XLOOKUP has that turned off by default. So this same XLOOKUP is returning no results.
What we have to do, the optional fourth argument here is If Not Found.
We'll leave that one out and then match mode right here.
Match mode is where we can specify that we want to use a wildcard match.
So I put a two in the fifth argument and then the XLOOKUP will work just like VLOOKUP.
As I was thinking about this problem that Mohammed sent in.
What happened if we got money from Bank of America account twice?
Hey, check out these new books from MrExcel, all of these were published this summer and available in print.
Click the "i" in the top right hand corner for more information about that.
If you have a situation where the same bank might have sent in money multiple times, well, then we're going to have to use, instead of VLOOKUP, a SUMIFS.
And luckily SUMIFS will handle the wildcards, just like the VLOOKUP would've done.
So we're looking through the amounts over in column F.
The data that we want to compare to is in column E, and then here, asterisk, ampersand, A4, ampersand, asterisk, and it successfully finds the totals from all of those. Let's just do a quick check here.
So Alt+Equals, this total's 3822, over here Alt+Equals 3822.
We got them all.
Well, I want to thank Mohammad 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's question from Mohammad on YouTube.
He was trying to look up the words “Bank of America” in text that says, "You have received money from the Bank of America account,".
Mohammad even had the asterisk here with the ampersands.
That's the whole trick right there. So here I have a list of banks here.
I have a list of text that has those banks in the middle of the text, and I want to get these amounts. So with VLOOKUP, the old VLOOKUP, it just works.
What we're looking up instead of A3 is quote, asterisks, quote, ampersand, A3, ampersand, quote asterisks, quote.
And then just make sure to put comma False at the end so we're looking for an exact match, an exact wild card match.
All right, and it works. Why does this one not work?
Because over here it says we got it from Mellon Bank and here it's BNY Mellon Bank.
The other reason that this would not work is if we had Character 160 spaces over here, like if this data came from the web, which it feels like it came from the web, then that would not work.
The place where it really becomes difficult is when we switch over to the new XLOOKUP.
VLOOKUP handled wild cards by default.
So that's asterisk for any number of characters, a question mark for a single character. There's also a special handling for the tilde.
If you're actually looking for an asterisk, you would have to put tilde asterisk, but XLOOKUP has that turned off by default. So this same XLOOKUP is returning no results.
What we have to do, the optional fourth argument here is If Not Found.
We'll leave that one out and then match mode right here.
Match mode is where we can specify that we want to use a wildcard match.
So I put a two in the fifth argument and then the XLOOKUP will work just like VLOOKUP.
As I was thinking about this problem that Mohammed sent in.
What happened if we got money from Bank of America account twice?
Hey, check out these new books from MrExcel, all of these were published this summer and available in print.
Click the "i" in the top right hand corner for more information about that.
If you have a situation where the same bank might have sent in money multiple times, well, then we're going to have to use, instead of VLOOKUP, a SUMIFS.
And luckily SUMIFS will handle the wildcards, just like the VLOOKUP would've done.
So we're looking through the amounts over in column F.
The data that we want to compare to is in column E, and then here, asterisk, ampersand, A4, ampersand, asterisk, and it successfully finds the totals from all of those. Let's just do a quick check here.
So Alt+Equals, this total's 3822, over here Alt+Equals 3822.
We got them all.
Well, I want to thank Mohammad 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.
