In this post, you’ll learn how to use the VLOOKUP With Wildcard in Google Sheets.
VLOOKUP With Wildcard
Imagine we have this table of employee information and we’ve been given a partial name (in this example: “Mye“) to find and retrieve information about.
To do this we use a standard VLOOKUP with wildcard asterisk character: *
The idea here is that we’ll search through the Last Name column to find the one starting with “Mye”, even though we don’t know the full surname.
We use this formula to achieve that, e.g. to look up first name:
G$2&"*" searches for the string “Mye*” where the * is known as a wildcard and represents a string of anything, or nothing, that could follow on after “Mye”. In other words, it would match “Mye”, “Myers”, “Mye123”, “MyeABC123!@#”,…etc.
The rest of the formula is just a regular
See the workbook here and feel free to make your own copy (File > Make a copy…).
If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.
VLOOKUP with two wildcards
What if our partial match potentially has missing characters before as well as after it?
No problem! We can simply add a second wildcard in front of the partial match and proceed as before. The formula for first name becomes:
Since the wildcard characters can also represent nothing (i.e. no characters), then it doesn’t matter if there turns out to be nothing in front of (or after) our partial string.
- It’s necessary to use FALSE as the last argument of your VLOOKUP in order for the wildcard matching to work.
- If there are multiple matches on the wildcard string in your data table, then the first one is returned, same as the usual application of VLOOKUP.
The workbook with single and double wildcard lookups is available here.
30 thoughts on “VLOOKUP With Wildcard In Google Sheets”
Would it be plausible to use of wildcards to search a month using either QUERY or VLOOKUP or would it be easier to use LEFT and RIGHT to identify a specific value (say month or year) to search for?
Must have missed this comment when you originally posted it!
Not sure exactly what you mean, but I haven’t tried a vlookup with a wildcard in a date (in place of a specific day?). It’s probably doable. Another option would be to use a query and specify that the date lie between a start date (end of prior month) and an end date (first day of the future month).
How can I do a wildcard, but only for certain amount of characters? Kinda like “match anything, but only if it’s under 6 characters”, not like wildcard, which would match from 0 to 1000000 characters.
With the data from this example, you could use a formula like this to return everything where the Last Name is 6 characters or less:
=ArrayFormula(query(if(len(A2:A51)<=6,A2:D51,""),"select * where Col1 is not null",0))
Hope that helps!
Ben, how come query works within arrayformula? 🙂
Mine: “=ARRAYFORMULA(IF(ROW(A:A)=1;”przelew?”;IF(ISBLANK(A:A);;QUERY(‘> bank’!A:J; “select Col2 where Col3 contains ‘”&B:B&”‘”;0))))”
is not working :/
I’ve tried with “B” instead of “Col2” too.
I have a list of jobs and a list of transfers. Transfer titles contain job IDs in one field, but in a very irregular manner, where only “contains” helps 🙂
Nice tip Ben. You saved me again.
As the information from Google forms is stored in a new line below the preexisting information, I would like to make an upward vertical lookup, in order to find the most recent information in case a value appearse more than once in the column. How can I achieve this?
It’s a little more complicated if you have multiple values in your lookup column, and I don’t have an example on this site. See if this helps: http://infoinspired.com/google-docs/spreadsheet/find-nth-occurrence-in-google-sheets/
This is great, but I am looking for the opposite. I want to have a lookup table where the partial text is in the first column and it checks to see if that text is contained in the source cell.
For example, the lookup table would have “St.” in the first column and “Street” in the second column, cell A1 would contain “Jones St.” and the VLOOKUP would return “Street” when it points to cell A1.
Is this possible?
This is why I’m here too! 🙂
Exact same for me as well. Any solution so far…?
Would love help on this one as well.
Add a new column with this formula. This will work when:
–you add this formula starting in row #6 and copy and paste down
–your column with “Jones St” (for example) is column A in the same sheet
–Your tab named “Vlookup table” has partial values in column A that you are checking for (such as “St” “Ave” “Blvd”)
–Your tab named “Vlookup Table” has the final result you want in column B
=vlookup( REGEXEXTRACT( A6,TEXTJOIN(“|”, 1,’ Vlookup table’!$a$5:$a$39) ), ‘Vlookup table’!$a$5:$b$39, 2, false)
Note that if you have several different particles that yield the same end result, put each of them on their own line. Each cell in column A should only have one text item.
Note that the order of your column with particles in the Vlookup tab is IMPORTANT. More specific items (ex “Ave”) should be at the top o fthe list, and more general particles that would possible cause false positive matches (ex “A”) should be at the bottom of the list (many street names could contain an “A” without being an “Avenue”).
This is a neat approach.
Have you considered putting a SORT inline? Then the source order would matter not.
How would I accomplish this if the data is on a separate tab? Thank you any help is appreciated.
Great info as usual. Wondering if there is a way to make this work with arrays.
Wondering if you might be able to help me figure out a way to make this array formula work? I can make it work if the SEARCH function > text_to_search argument is a single cell, but not if it’s an array. I want it to be an array so it auto-applies to any new row added to the spreadsheet.
Below works (note A2 in the SEARCH function). Cell C2 on Raw Data tab.
Below doesn’t work (note A2:A in SEARCH). The array formula expands to all new cells, but the returned values are blank. If I change the bold A2:A to just A2, it always returns the value for cell A2’s match, not A3, A4, etc for each corresponding row.
Seems like Index / Match doesn’t work with Arrays and now I found your post here I wonder if I could do it with vlookups of partial string matches like you have here, but as an array instead of just one value to vlookup.
Thanks so much.
Here is a link to the test data sheet. https://docs.google.com/spreadsheets/d/1slUiZdDKLNDRfismNj9B7_YAhcQuIJBW4JBcEa8l2UE/edit?usp=sharing
Thanks for this, the first wildcard instance works, but it fails to return the value when the wildcard is not needed?
Ie. if I’m searching for
“12345*”, it will return the value fine (it’s searching for
12345 A, or even 12345 B)
but if a plain 5 digit number like 12345 will return a #N/A error.
The reason for the need for the wildcard, is some items have just 5 digits, whilst some may have an A, B, C or even D added on after (with a space)
Is there a way to apply this as a data validation for a column? I want any new entries to be checked for partial matches. For example, if someone put “dog” and someone else puts “the dog” lower down in the column, it would prevent “the dog” from being entered.
I have a sheet with column J as reference and to highlight partial matches in column B using Column J as reference
Sheet link https://docs.google.com/spreadsheets/d/1e1Mt0XAOnK_t-COPcxyftBT2vFw8A2ifqGVeJPgB0CE/edit?usp=sharing
Hi, This page: https://support.google.com/docs/answer/3093480?hl=en-GB is slightly wrong.
“* to match zero or more contiguous characters” – this is incorrect because * doesn’t match an empty cell. How can I match any cell, whether it has a value has no value?
I have a similar problem I need help with. My vlookup is written as =arrayformula(VLOOKUP(“*”B2:B&”*”,’RCA Responses’!$AA$2:$AR$73,18,FALSE))
Now, column B has a single entry such as 3000 or 3500.
RCA responses column may have multiple entries such as 3000, 3500
It will return t eh correct response when there are more than 1 entry, but when there’s only 1 entry I get this error:
Did not find value ‘*3000*’ in VLOOKUP evaluation.
It’s adding * into the serach criteria when there’s only a single entry, but not when there are multiple.
Any thoughts please would be greatly appreciated.
Can vlookup() return the matching cell position (ie. row number)?
You should use match() function if the result you’re looking for is the position of the matching cell.
thank you sir. been looking for this substring search everywhere. may god give u wealth & happiness . Thank you very much
What if I need to vlookup using a partial match on the reference side?
Specifically I’m trying to get a bunch of ‘bad’ data to conform to a lookup list of valid values… so I thought I could create a reference table of ‘good’ values with an extra column that just has a snippet of the correct text… and match the ‘bad’ value with the snippet with a “*” either side… is this possible
Hi Ben, thank you for posting this. It’s very usefull!
Is it possible to sequence this formula after the exact match doesn’t return results.
First I want to find the exact match. If there is none, then I would like to find the partial match.
Just came across this and the REGEXTRACT solutio solved my problem but one word of caution. If the lookup table column that is the key has an asterisk in it, the formula doesn’t work. I think this is because asterisk is a valid regex character so there probably other characters that cause it to fail.
Yes, the asterisk * is a special REGEX character (meaning zero or more matches), so if you want to actually search for the asterisk itself, use the escape character \ before it. I.e. search for “\*” not “*” in your REGEX rule.
This wildcard is a super helpful tool.
The main limitation of traditional string matching tricks is that they cannot take into consideration similarities outside of the strings. Topic clustering requires semantic understanding.
I published a pre-trained model that has the intuition of GPT-3 and the join capabilities of fuzzy matching. Use it like vlookup or index match to speed up your topic clustering work in google sheets.