Excel Partial Text Lookup Formulas. Top 5 methods for Fuzzy Lookup. Excel Magic Trick 1691.

fuzzy look up partial text lookup approximate match lookup and wild card lookup those are all synonyms for two different types of lookup the first situation is we have coca-cola and we need to find a match in this case coca-cola incorporated and return atlanta the lookup value is the sub text string and the item we're trying to match is the full text string down here the lookup value is the full text string but we need to match it with a subtext string and return the gender [Music] now for each type of lookup we'll see how to do it with the new microsoft 365 and we'll see how to do it the old school method well if you have microsoft 365 excel we get to use x lookup now for the lookup value we're going to put in coca-cola but the problem is right now is x lookup needs to know that there might be some text before and after this text and the way we do that is with a wild card we need to use the asterisk character that represents zero or more characters the question mark which we're not going to use here represents a single character and since we're going to have to join it to that cell reference we have to put it in double quotes so asterisks and double quotes and then join it to the relative cell reference then we do it on the other side so the beauty of this wild card is if it represents zero or more well it can be straight coca-cola or any number of characters before or after now comma look up array we want to try and find a match in that column comma return array we're trying to get the city comma and if not found that's directly inside of x lookup in the old days with vlookup we'd have to use if an a function or even the older days just to straight if so i'm going to put in double quotes not found and then comma the fourth argument match mode exact and actually these two are what we use for approximate match lookup that's when we have tax rates or commission rates this is a loose synonym that sometimes people use for partial text lookup or fuzzy lookup but for us we want two that'll allow this x lookup function to understand that we want to use a wildcard now the last argument we don't need so i'm going to not put that argument in close parentheses now if i ctrl enter that just puts the formula in the cell and keeps the cell selected that works perfectly but if we have microsoft 365 excel f2 to put it back in edit mode instead of locking both of these and using a relative cell reference we'll just give it all four values that instructs xlookup to spill all four answers so we avoid having to lock and we avoid having to manually copy so now when i control enter it spills down the column now anytime you use a spilled array the formula only lives in the top cell you notice all the cells below are grayed out all right old school well we can use vlookup and we use our wild card and we join it on both sides comma table array well we have to have the whole table and we have to lock it so i hit the f4 key comma column index number well the thing i want to go and get and bring back to the cell is in the second column so i type a 2 comma we're doing exact match so we either put false or zero close parentheses control enter double click and send it down since vlookup doesn't have an argument for what to put in the cell if there's an error f2 and 2007 or later you can use if error 2013 or later you can use if n a so it's going to check whatever that is if it's an n a then at the end we just put whatever we want i'm going to put not found in double quotes control enter double click and send it down so that's how to go from a subtext string to the full text string if we want to do the reverse meaning the text we have is bigger than the text over here we can't use a wildcard but we can use the search function now the search function normally takes a single text item like john in find text and then within text we point it to this item but guess what find text we're going to give it a function argument array operation we're going to give it all three values that way if it finds one it'll tell us the position meaning john was found within here so that would be position one two three four five and then the other two will get errors but that pattern of position or errors will help us pick the item from the second column and then we comma and within text we'll have the full text string now if i close parentheses and hit the f9 key there's our pattern of errors and positions so what are we interested in the number and notice the numbers in the first position which is exactly the gender we need to bring back control z is number at the end close parentheses f9 now we have a pattern of trues and falses and there's a few things we can do here in microsoft 365 i'm going to use filter and i want to filter this range right here comma and then the include that's the pattern of trues and falses and then at the end say not found now if i control enter that works but guess what i can't do a function argument array operation here because then i'm doing two different size arrays and it doesn't like that so in this case we'll leave that as relative and come back here i'm going to click on find text to highlight the whole argument and then hit the f4 key same here sometimes the problem is when you come back and hit the f4 key uh oh like that it only does one of them so the safe thing is to select using the screen tip the whole range and then hit the f4 key now i can control enter double click and send it down last cell f2 now escape we could use x lookup if i copy so i'm using the screen tip to click select everything so i can easily copy it ctrl c i like filter better but we could use x lookup and look up true comma ctrl v within that range there comma and there's our return range f4 comma if not found something in uh some message there and double quotes close parentheses control enter double click and send it down now for old school well we're going to use that that's as old school as they get equal sign control v and we'll use match the old match because we're assuming we're in the old school and we'll look up true comma within there comma 0 for exact match that will give us the relative position and then we have to use that inside of index we're trying to get these values here f4 comma and then there's the row number close parentheses control enter double click and send it down now this is a case where we could use if air or if n a but it is not efficient because if error and fna always have to run the whole formula in these circumstances if you can find an alternative logical test which is shorter than running this whole formula then it's more efficient to use the if function with that logical test now we want to highlight and select just the search part because we can use that control c inside of if in the first argument logical test if i control v comma just for a second remember f9 what that gives us an array of numbers and errors and what happens when there's no match it's all errors well guess what control z the count function can count and it's going to count how many numbers there are and it will not be bothered by the fact that they're errors it'll just deliver in our case either one or zero so if i select it and hit the f9 key we get a one down here we'll get a zero and that'll be interpreted as false so if it's a non-zero number it'll run that otherwise at the end comma in double quotes not found close parentheses control enter double click and send it down all right that was a little fun with old school and new school when we have the full text string and we want to look up and match some sub text string and we saw how to use x lookup in a wild card or vlookup in a wild card when the lookup value is the subtext string and the item to match is the full text string all right if you like that video be sure to click that thumbs up leave a comment and subscribe because there's always lots more videos to come from excel is fun and if you want to check out some videos about more look up tricks here's some videos [Music] you

Channel: ExcelIsFun

Views: 16,485

Rating: 4.9730458 out of 5

Keywords: Excel, Highline College, Mike Girvin, excelisfun, excel is fun, excel fun, excel magic, Excel Formulas, Excel Functions, formulas and functions, Excel Magic Trick 1691, EMT 1691, Partial Text Lookup, Fuzzy Lookup, Excel Wild Cards, Wild Card Lookup, Contains Criteria, Contains Logical Test, XLOOKUP function, VLOOKUP function, INDEX Function, MATCH function, FILTER function, SEARCH function, FIND Function, ISNUMBER function, IFNA function when to not use IFERROR function

Id: bIAmSdklRdo

Length: 9min 55sec (595 seconds)

Published: Mon Sep 21 2020