📑 LIKE String Operator in Google Sheets Query and Wildcard Use

Bookmarked LIKE String Operator in Google Sheets Query and Wildcard Use (InfoInspired)

The LIKE string operator in Google Sheets Query is useful for complex comparisons. It helps you use two wildcards in Google Sheets Query.

I know that it is possible to use wildcards in VLOOKUPS, however I was wondering for a means of incorporating a wildcard into a QUERY. Ideally, I wanted to replace a letter with a wildcard, but I did find a couple of posts discussing the use of LIKE to incorporate % or _ options. For example:

=query(A2:A,"Select A where A like 'A%'")

As well as MATCH to utilise REGEX:

=query(A1:B, "Select * where B matches 'India|Russia' ")

Rather than an OR statement:

=query(A1:B, "Select * where B = 'India' or B = 'Russia'")

Using REGEX also allows you to match a substring anywhere in a string:

=query(A1:B, "Select * where B matches '.*India.*' ")

Although I was still stuck with my initial problem (might need to explore the use of an IF statement), however I did pick up a few more possibilities.

2 responses on “📑 LIKE String Operator in Google Sheets Query and Wildcard Use”

  1. In this newsletter, we’re looking at how to use hyperlinks in Google Sheets. And, if you’re an advanced user, I’ve got a spicy formula for you at the end of the newsletter.

    Sheets Tip #312: The Missing Link 🔗 by Ben Collins

    Ben, I really like the spicy solution regarding hyperlinks:

    =HYPERLINK( "#gid=0&range=A" & ArrayFormula( MAX( IF( ISBLANK(A2:A), 0, ROW(A2:A))))+1 , "Add Data")

    I had never thought about the fact that you can reference a cell and what possibilities that may allow.

    It had me wondering about linking to a cell in a table via a drop-down menu / wild card search using the MATCH function to add a link to a table row:

    =HYPERLINK("gid=0#gid=0&range=B" & MATCH(B$1, 'Sheet1'!B2:B, 0) + ROW('Sheet1'!B2) - 1, B1)

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *