## VLOOKUP with a single 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

formula with the wildcard asterisk character: **VLOOKUP*******

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:

`=VLOOKUP(G$2&"*",$A$1:$D$51,2,FALSE)`

The `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 `VLOOKUP`

.

See the workbook here and feel free to make your own copy (**File > Make a copy…**).

## 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:

`=VLOOKUP("*"&G$2&"*",$A$1:$D$51,2,FALSE)`

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.

**Notes:**

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

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?

Hey Aaron,

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

Ben

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.

Hi,

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

Nice tip Ben. You saved me again.

cheers

Mike

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?

Hi Rafael,

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/

Cheers,

Ben

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?

How would I accomplish this if the data is on a separate tab? Thank you any help is appreciated.

Y’all should absolutely look at the Flookup add-on.

It’s versatile and holds up really well in handling approximate match cases.