Wildcards in Vlookup Search Range in Google Sheets

Published on

Here is a workaround to use wildcards in Vlookup Range in Google Sheets. The workaround shows the alternative to asterisk wildcard use in the range.

The WILDCARDS are for partial matching in spreadsheet formulas. But it won’t work with all the functions.

Regarding Vlookup, as far as I know from my extensive testing, we can only ‘directly’ use WILDCARDS with the search key.

The search key is the first argument in Google Sheets Vlookup. The second argument is the range.

From the below syntax, you can understand the positioning of the ‘arguments’ in the function.

Syntax: VLOOKUP(search_key, range, index, [is_sorted])

As a side note, I’ve already explained the use of wildcards with the search_key in Vlookup in Google Sheets. Here is that post – Partial Match in Vlookup in Google Sheets [Text, Numeric and Date].

We can’t use the wildcards in the Vlookup range in Google Sheets. To overcome this, we should use some kinds of workaround formula.

Example Data - Lookup Range and Search Keys

For example, if we want to use the search_key “123 xyz” to search it in the range A2:A and return the value from B2:B, we will possibly use the below Vlookup in F2.

=vlookup(E2,A2:B,2,0)

No doubt, it would return an #N/A error indicating the search key is not present in the range.

We can’t use wildcards in the Vlookup range as below in Google Sheets!

=ArrayFormula(vlookup(E2,{"*"&A2:A&"*",B2:B},2,0))

You will get the same above error.

How to Use Wildcards in Vlookup Range in Google Sheets

Using three functions as a combination we can solve this issue. The functions don’t involve Vlookup. The said functions are Regexmatch, Filter, and Index.

Must Check: Google Sheets Function Guide [Quickly Learn All Popular Functions].

Use the below formula, as an alternative to the wildcards in Vlookup range in Google Sheets, in cell F2, and drag down.

=index(
     FILTER(
        $B$2:$B,
        REGEXMATCH(lower(E2),lower($A$2:$A))
     ),
     1,1
)
Formula Alternative to Wildcards in Vlookup Range

It is also called partial RANGE match in Vlookup in Google Sheets. Let’s learn the formula in detail below.

The role of the LOWER function is minimal in the formula as it’s for making the formula case insensitive.

Below, I am explaining the functionality of the other Google Sheets functions present in detail.

We must read the formula from the middle, and that is the Regexmatch formula here.

Regexmatch to Partial Match in Vlookup Range in Google Sheets

Formula Part 1:

REGEXMATCH(lower(E2),lower($A$2:$A))

Don’t try this formula in your sheet as it won’t return the correct result.

Using the above part, what we want is to partially match the value in the cell E2 (“123 xyz”) in the range A2:A.

Please note that the above part of the formula is within the FILTER in the main formula. Outside FILTER, to test the above Regexmatch formula, you must use it as an array formula as below.

=ArrayFormula(REGEXMATCH(lower(E2),lower($A$2:$A)))
Partial Range Matching Using Regexmatch

The formula returns TRUE in the row that contains the partial match of the search key, i.e., “123 xyz”, in the Vlookup range (we can ignore the TRUE in blank rows).

FILTER Only the Boolean TRUE Values

It is the main part of the ‘workaround’ use of wildcards in the Vlookup search range in Google Sheets.

Let’s filter B2:B, the Vlookup result (index) column, wherever Regexmatch returns the Boolean TRUE values. The below formula does that.

Formula Part 1 and 2:

=filter($B$2:$B,ArrayFormula(REGEXMATCH(lower(E2),lower($A$2:$A))))

As per the FILTER syntax, the above Regexmatch formula is the ‘condition’ here.

Syntax: FILTER(range, condition1)

Note:- If you want to filter FALSE values, then you should specify that in the condition. For TRUE, it’s not necessary.

We can remove the ArrayFormula as the Regexmatch is within Filter. So here is the shortened form of the above part 1 and 2 formula.

=filter($B$2:$B,REGEXMATCH(lower(E2),lower($A$2:$A)))

What’s the Role of the Index Then in the Final Formula?

Sometimes, the above Filter may return two or more values in case there are multiple matches of the same search_key.

We Want the first one as per the Vlookup standard.

The INDEX does that part wonderfully.

That’s all about the use of Wildcards in Vlookup Search Range in Google Sheets.

Thanks for the stay. Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.