VLOOKUP Hack #9: Partial Match

Рет қаралды 98,765

6 жыл бұрын

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/

Пікірлер: 71

@esmermanubay7664 Жыл бұрын

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

@shiwei94 4 күн бұрын

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

@Up4Excel 2 жыл бұрын

Great technique Jeff, thanks for the examples 👍

@ishratchowdhury4938 3 жыл бұрын

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

@ginger.schooling 2 жыл бұрын

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

@ExcelU 2 жыл бұрын

Yay ... glad it was helpful :)

@crisjohnordanel 3 жыл бұрын

Found it finally. Thanks for this video Sir

@PaulEBrownbill Жыл бұрын

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

@ExcelU Жыл бұрын

Thanks ... and glad it was helpful!

@lovetrain1234567890 6 ай бұрын

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

@clarissaalessandra5788 3 жыл бұрын

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

@nitinpujari9253 2 ай бұрын

Subscription added. Thanks!

@oyierem Жыл бұрын

Excellent!!

@ExcelU Жыл бұрын

Thank you :)

@justanotheruser206 5 ай бұрын

nice stuff dude, really help

@caribbeanpharoah9269 Жыл бұрын

This is amazing, thank you so much, subscribed!

@ExcelU Жыл бұрын

Glad it was helpful :)

@higiniomartinez6971 2 жыл бұрын

thank you very much! , greetings from Mexico ✌🏻

@arunkumark6351 3 жыл бұрын

Simply Awesome. Thank you so much!!

@ExcelU 3 жыл бұрын

Glad it helped Arun ... thank you!

@sonnyuntung3418 7 күн бұрын

thanks a bunch, saves me a lot of time

@ilangopandiyan7718 2 жыл бұрын

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

@ExcelU 2 жыл бұрын

Thanks ... glad it was helpful :)

@h4niali 11 ай бұрын

Thank you very much, it's helpful

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

@rittuvarghese1 2 жыл бұрын

Thanks Sir. Good information

@GodFamilyWorkGym 6 ай бұрын

Thank you much!

@georgecleare9460 2 ай бұрын

Thank you!!

@mokhelemahlase3647 2 жыл бұрын

Great Hack. Thanks for sharing.

@ExcelU 2 жыл бұрын

Thanks ... glad it was helpful!

@amenhotepthethird209 Жыл бұрын

Brilliant, worked for me, thanks.

@ExcelU Жыл бұрын

Glad it helped!

@madushankaweeriyasinghe2833 3 жыл бұрын

Simply awesome sir, thanks from Sri Lanka

@ExcelU 3 жыл бұрын

Thanks!

@oofywoofy3173 Жыл бұрын

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

@anastaysia 2 жыл бұрын

Thank you!

@magdahassib694 8 ай бұрын

Great 👍 thanks

@yogeshglimbarkar5307 3 жыл бұрын

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

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

@VVeiQuek 5 ай бұрын

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 5 ай бұрын

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.

@diala_the_bookkeeper 3 жыл бұрын

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?

@MohanKumarmohanthemass 5 ай бұрын

Thanks 👍

@aditimantri5468 3 жыл бұрын

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

@Sumee21051987 Жыл бұрын

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

@crazychildren1233 13 күн бұрын

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

@richbarrier 3 ай бұрын

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

@koulchilebaiz Жыл бұрын

just THANK YOU

@ExcelU 11 ай бұрын

Glad it was helpful :)

@vedantsartcraftanddrawinga4804 2 жыл бұрын

thanks

@ExcelU 2 жыл бұрын

Welcome :)

@nandusguppyfarm 3 жыл бұрын

You are great.

@ExcelU 3 жыл бұрын

Thanks!

@fda.r5628 17 күн бұрын

Thanks ❤❤❤❤

@moi984 2 жыл бұрын

Finally my awnser!

@ExcelU 2 жыл бұрын

Yay ... glad it helped!

@parajf 2 жыл бұрын

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

@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: kzfaq.info/get/bejne/sMdlpJOensudaIk.html Skip ahead to about minute 12. Hope it helps!