Thank you for the video! I tried to use the contains option but it's not working. I've tried everything and can't seem to find a way to extract information from a cell using a table of keywords
Thanks! Had trouble making this work figured it out a few minutes later. For some reason the numbers had to be on my second column while the text needs to be on the first column of the table, interchanging them breaks the formula. And yes, I'm also modifying what column value I need to return but I just couldn't do it the other way.
Great video! What if "what you want to look up" contains a part of a different value from "where you want to look for it"? In your example, let's say, I want to look up a value called "North Region Amount" in B2? Thanks.
This question looks so simple to be unanswered for 2 years. but only until drafting the explanation, I find that your question is quite ambiguous. 1. Not just `~ Amount`, say the list also contains `~`, `~ Revenue`, `~ [Any word]` besides `~ Subtotal`, Excel would look up values the same alphabetically way, So, using the wildcard would return unexpected result. By then, running into error/warning is a blessing, than it generates the wrong value 2. The video demonstrates the situation where the region (category) matters more than their homogenous attributes. In your case, if the type/nature of transaction matters more than the region, the lookup value would have been `"*" & "Amount"`. 3. If the lookup table has both components being the variable, like `West Amount`, `West Subtotal`, `North Amount`, `West Subtotal` etc., there is no reason to look up by partial string match.
Also, to the creator, I am so awed by this. I only learned about this trick today, more than a decade when I learned VLOOKUP lols. But like what the question reflects, the use is quite dependent on the lookup data. Also, also, shouldn't the lookup value be just `North` since `Region Subtotal` would be same throughout? haha Hope your sharing would reach more people soon.
Can you make a video that will return a partial string such as 'Ft G Meade, MD' from a text string 'Fort George Meade, MD'? Both are sources from different tables. Both table values will be joined together.
What if you want to get a total all of columns that contain "region"? I have hundred of rows of data that require me to extract sales #s of all row that contain X in Column A
Rather than VLOOKUP, you may want to consider using SUMIFS. I do have a video about it here: ua-cam.com/video/za5q0m9e98U/v-deo.html Skip ahead to about minute 12. Hope it helps!
Very nice video sir. But i have a problem while compiling result of std 11 , 1 student have written his surname first in Physics paper and same student written his surname lastly. So how to use vlookup in this situation. Pls guide me. Thanks in advance
WOW !!!! This saved me ALOT of time you got a new subscriber
Is there a way to best match with vlookup if there are spelling errors, such as “north region” and “north regool”
Thank you for the video! I tried to use the contains option but it's not working. I've tried everything and can't seem to find a way to extract information from a cell using a table of keywords
Thanks! Had trouble making this work figured it out a few minutes later. For some reason the numbers had to be on my second column while the text needs to be on the first column of the table, interchanging them breaks the formula. And yes, I'm also modifying what column value I need to return but I just couldn't do it the other way.
what if its the other way around? what if the value in table is shorter?
My problem
how did you mark as table 1 , table 3 ?
Great video! What if "what you want to look up" contains a part of a different value from "where you want to look for it"? In your example, let's say, I want to look up a value called "North Region Amount" in B2? Thanks.
This question looks so simple to be unanswered for 2 years. but only until drafting the explanation, I find that your question is quite ambiguous.
1. Not just `~ Amount`, say the list also contains `~`, `~ Revenue`, `~ [Any word]` besides `~ Subtotal`, Excel would look up values the same alphabetically way, So, using the wildcard would return unexpected result. By then, running into error/warning is a blessing, than it generates the wrong value
2. The video demonstrates the situation where the region (category) matters more than their homogenous attributes. In your case, if the type/nature of transaction matters more than the region, the lookup value would have been `"*" & "Amount"`.
3. If the lookup table has both components being the variable, like `West Amount`, `West Subtotal`, `North Amount`, `West Subtotal` etc., there is no reason to look up by partial string match.
Also, to the creator, I am so awed by this. I only learned about this trick today, more than a decade when I learned VLOOKUP lols.
But like what the question reflects, the use is quite dependent on the lookup data.
Also, also, shouldn't the lookup value be just `North` since `Region Subtotal` would be same throughout? haha
Hope your sharing would reach more people soon.
What if there are numbers instead of a string? Will that still work ?
Can you make a video that will return a partial string such as 'Ft G Meade, MD' from a text string 'Fort George Meade, MD'? Both are sources from different tables. Both table values will be joined together.
Can someone help: I need to swich (DATA AND PURPOSE). How can i Do it?
What is that wild card? Can anyone tell me exact formalla
Brilliant 🎉thank you so much for doings this video. How to write the wild card for contains when doing countif on a list of values is so time saving!
what if I have a first name and last name in one sheet and another sheet with first name father name and last name. how do I solve that?
What if you want to get a total all of columns that contain "region"? I have hundred of rows of data that require me to extract sales #s of all row that contain X in Column A
Rather than VLOOKUP, you may want to consider using SUMIFS. I do have a video about it here: ua-cam.com/video/za5q0m9e98U/v-deo.html
Skip ahead to about minute 12. Hope it helps!
What if we want in reverse order, that is we want north in D12 ROW
Great video, especially the 'contains' part of the wildcard function, thanks Paul
Thanks ... and glad it was helpful!
What if I want to find 'region subtotal north' ?
Didn't know I can use wildcard. Now I know. Thanks Jeff!
omg this is great! you saved me hours of work here
Great technique Jeff, thanks for the examples 👍
Very nice video sir.
But i have a problem while compiling result of std 11 ,
1 student have written his surname first in Physics paper and same student written his surname lastly.
So how to use vlookup in this situation.
Pls guide me.
Thanks in advance
Thank you for the contribution Sir, You're a lifesaver!
Hi it was so simple yet awesome hack...thanks man !!
Thanks ... glad it was helpful :)
LOVE IT! exactly what I was wondering about. Thank you!
Yay ... glad it was helpful :)
This is amazing, thank you so much, subscribed!
Glad it was helpful :)
thank you very much! , greetings from Mexico ✌🏻
Excellent!!
Thank you :)
2:40
Great Hack. Thanks for sharing.
Thanks ... glad it was helpful!
Found it finally. Thanks for this video Sir
Brilliant, worked for me, thanks.
Glad it helped!
Subscription added. Thanks!
Simply awesome sir, thanks from Sri Lanka
Thanks!
Simply Awesome. Thank you so much!!
Glad it helped Arun ... thank you!
nice stuff dude, really help
thanks a bunch, saves me a lot of time
Thanks Sir. Good information
Thank you very much, it's helpful
Omg omg omg this is it you saved me!!!
Finally my awnser!
Yay ... glad it helped!
Thank you!
Great 👍 thanks
Thank you much!
Thanks ❤❤❤❤
Thanks 👍
Great video
Thank you :)
You are great.
Thanks!
thanks
Welcome :)
This is Awesome.....
Thank you!
Life saver
Glad it helped :)
just THANK YOU
Glad it was helpful :)
Thank u
You are very welcome!
Welcome :)
I'm a P-ro now 😅👍
🎉
Thank you!!