VLOOKUP Hack #9: Partial Match

File Download: www.excel-univ...
Learn Excel Fast:
www.excel-univ...
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-univ...

[@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.

[@ishratchowdhury4938]

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

[@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!

[@esmermanubay7664]

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

[@PaulEBrownbill]

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

[@ExcelU]

Thanks ... and glad it was helpful!

[@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

[@shiwei94]

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

[@ilangopandiyan7718]

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

[@ExcelU]

Thanks ... glad it was helpful :)

[@oyierem]

Excellent!!

[@ExcelU]

Thank you :)

[@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.

[@mokhelemahlase3647]

Great Hack. Thanks for sharing.

[@ExcelU]

Thanks ... glad it was helpful!

[@sonnyuntung3418]

thanks a bunch, saves me a lot of time

[@crazychildren1233]

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

[@adarshyalgar4555]

What is that wild card? Can anyone tell me exact formalla

[@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.

[@lovetrain1234567890]

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

[@caribbeanpharoah9269]

This is amazing, thank you so much, subscribed!

[@ExcelU]

Glad it was helpful :)

[@vedantsartcraftanddrawinga4804]

thanks

[@ExcelU]

Welcome :)

[@oofywoofy3173]

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

[@Up4Excel]

Great technique Jeff, thanks for the examples 👍

[@Sumee21051987]

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

[@amenhotepthethird209]

Brilliant, worked for me, thanks.

[@ExcelU]

Glad it helped!

[@higiniomartinez6971]

thank you very much! , greetings from Mexico ✌🏻

[@ginger.schooling]

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

[@ExcelU]

Yay ... glad it was helpful :)

[@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?

[@parajf]

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

[@GodFamilyWorkGym]

Thank you much!

[@georgecleare9460]

Thank you!!

[@arunkumark6351]

Simply Awesome. Thank you so much!!

[@ExcelU]

Glad it helped Arun ... thank you!

[@aditimantri5468]

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

[@nitinpujari9253]

Subscription added. Thanks!

[@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

[@rittuvarghese1]

Thanks Sir. Good information

[@justanotheruser206]

nice stuff dude, really help

[@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: ua-cam.com/video/za5q0m9e98U/v-deo.html
Skip ahead to about minute 12. Hope it helps!

[@h4niali]

Thank you very much, it's helpful

[@magdahassib694]

Great 👍 thanks

[@MohanKumarmohanthemass]

Thanks 👍

[@crisjohnordanel]

Found it finally. Thanks for this video Sir

[@oeindrilasarkar3541]

Life saver

[@ExcelU]

Glad it helped :)

[@koulchilebaiz]

just THANK YOU

[@ExcelU]

Glad it was helpful :)

[@richbarrier]

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

[@madushankaweeriyasinghe2833]

Simply awesome sir, thanks from Sri Lanka

[@ExcelU]

Thanks!

[@fda.r5628]

Thanks ❤❤❤❤

[@anastaysia]

Thank you!

[@moi984]

Finally my awnser!

[@ExcelU]

Yay ... glad it helped!

[@nandusguppyfarm]

You are great.

[@ExcelU]

Thanks!

[@cianog]

Great video

[@ExcelU]

Thank you :)

[@josephlesliekj9223]

🎉

[@itsjustKRL]

2:40

[@nishanth4323]

I'm a P-ro now 😅👍

[@sreedipsarkar5216]

This is Awesome.....

[@ExcelU]

Thank you!

[@kiranpallan6948]

Thank u

[@ExcelU]

You are very welcome!

[@ExcelU]

Welcome :)