VLOOKUP Hack #9: Partial Match

File Download: www.excel-university.com/yt_q...
Learn Excel Fast:
www.excel-university.com/yt
Let's say we want VLOOKUP to match the lookup value "North Region" with "North Region Subtotal" stored in the lookup range. We started this series by looking at the 4th argument. We know it can be TRUE or FALSE. FALSE means exact match and TRUE means approximate match. So, what exactly is an approximate match? Well, as you may have guessed, we'll dig into that, and, hack the 1st argument to accomplish our true objective: a partial match.
Subscribe to blog and view all posts:
www.excel-university.com/blog/

[@qwertytwerqy2440]

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.

[@amberswartz3876]

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!

[@Up4Excel]

Great technique Jeff, thanks for the examples 👍

[@shiwei94]

Thank you for the contribution Sir, You're a lifesaver!

[@esmermanubay7664]

Didn't know I can use wildcard. Now I know. Thanks Jeff!

[@clarissaalessandra5788]

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

[@ginger.schooling]

LOVE IT! exactly what I was wondering about. Thank you!

[@ExcelU]

Yay ... glad it was helpful :)

[@ishratchowdhury4938]

what if its the other way around? what if the value in table is shorter?

[@crisjohnordanel]

Found it finally. Thanks for this video Sir

[@lovetrain1234567890]

omg this is great! you saved me hours of work here

[@chris-zu6sf]

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.

[@nitinpujari9253]

Subscription added. Thanks!

[@PaulEBrownbill]

Great video, especially the 'contains' part of the wildcard function, thanks Paul

[@ExcelU]

Thanks ... and glad it was helpful!

[@higiniomartinez6971]

thank you very much! , greetings from Mexico ✌🏻

[@caribbeanpharoah9269]

This is amazing, thank you so much, subscribed!

[@ExcelU]

Glad it was helpful :)

[@yogeshglimbarkar5307]

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

[@justanotheruser206]

nice stuff dude, really help

[@arunkumark6351]

Simply Awesome. Thank you so much!!

[@ExcelU]

Glad it helped Arun ... thank you!

[@sonnyuntung3418]

thanks a bunch, saves me a lot of time

[@aditimantri5468]

What if I want to find 'region subtotal north' ?

[@diala_the_bookkeeper]

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?

[@h4niali]

Thank you very much, it's helpful

[@oofywoofy3173]

Is there a way to best match with vlookup if there are spelling errors, such as “north region” and “north regool”

[@francesluo6324]

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.

[@VVeiQuek]

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.

[@VVeiQuek]

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.

[@ilangopandiyan7718]

Hi it was so simple yet awesome hack...thanks man !!

[@ExcelU]

Thanks ... glad it was helpful :)

[@Sumee21051987]

What if there are numbers instead of a string? Will that still work ?

[@oyierem]

Excellent!!

[@ExcelU]

Thank you :)

[@rittuvarghese1]

Thanks Sir. Good information

[@madushankaweeriyasinghe2833]

Simply awesome sir, thanks from Sri Lanka

[@ExcelU]

Thanks!

[@crazychildren1233]

Can someone help: I need to swich (DATA AND PURPOSE). How can i Do it?

[@parajf]

What if we want in reverse order, that is we want north in D12 ROW

[@amenhotepthethird209]

Brilliant, worked for me, thanks.

[@ExcelU]

Glad it helped!

[@GodFamilyWorkGym]

Thank you much!

[@georgecleare9460]

Thank you!!

[@anastaysia]

Thank you!

[@mokhelemahlase3647]

Great Hack. Thanks for sharing.

[@ExcelU]

Thanks ... glad it was helpful!

[@magdahassib694]

Great 👍 thanks

[@peteroliver4988]

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

[@ExcelU]

Rather than VLOOKUP, you may want to consider using SUMIFS. I do have a video about it here: ruclips.net/video/za5q0m9e98U/видео.html
Skip ahead to about minute 12. Hope it helps!

[@MohanKumarmohanthemass]

Thanks 👍

[@richbarrier]

Omg omg omg this is it you saved me!!!

[@nandusguppyfarm]

You are great.

[@ExcelU]

Thanks!

[@fda.r5628]

Thanks ❤❤❤❤

[@sreedipsarkar5216]

This is Awesome.....

[@ExcelU]

Thank you!

[@moi984]

Finally my awnser!

[@ExcelU]

Yay ... glad it helped!

[@cianog]

Great video

[@ExcelU]

Thank you :)

[@koulchilebaiz]

just THANK YOU

[@ExcelU]

Glad it was helpful :)

[@oeindrilasarkar3541]

Life saver

[@ExcelU]

Glad it helped :)

[@vedantsartcraftanddrawinga4804]

thanks

[@ExcelU]

Welcome :)

[@kiranpallan6948]

Thank u

[@ExcelU]

You are very welcome!

[@ExcelU]

Welcome :)

[@josephlesliekj9223]

🎉

[@itsjustKRL]

2:40

[@nishanth4323]

I'm a P-ro now 😅👍