VLOOKUP Hack #9: Partial Match

Learn Excel Fast:

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:

[amberswartz]

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!

[qwertytwerqy]

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.

[esmermanubay]

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

[ishratchowdhury]

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

[UpExcel]

Great technique Jeff, thanks for the examples 👍

[shiwei]

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

[sonnyuntung]

thanks a bunch, saves me a lot of time

[ilangopandiyan]

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

[lovetrain]

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

[caribbeanpharoah]

This is amazing, thank you so much, subscribed!

[francesluo]

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.

[ginger.schooling]

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

[clarissaalessandra]

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

[higiniomartinez]

thank you very much!, greetings from Mexico ✌🏻

[adarshyalgar]

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

[crazychildren]

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

[chris-zusf]

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.

[crisjohnordanel]

Found it finally. Thanks for this video Sir

[oofywoofy]

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