Simulating a LIKE function

By in Uncategorized on .

Excel doesn’t have a LIKE function, but you can use the COUNTIF function to pretend that it does. If LIKE existed, you might test the pattern of a cell like this


to return True for an entry in A1 like “DD123F6?. The same syntax can be used for COUNTIF


to return 1 if the pattern matches and 0 if it doesn’t. By limiting the range argument of COUNTIF to one cell, it acts somewhat like a LIKE function would.

Using COUNTIF, you don’t have the range of patterns that are available to you with the Like operator in VBA. You basically just have the * and ? wildcard characters. But it’s better than nothing.

11 thoughts on “Simulating a LIKE function

  1. J-Walk

    And if you don’t mind a little VBA, this function simulates Like exactly:

    Function ISLIKE(arg, pattern As String) As Boolean
    ‘ Returns true if the first argument is like the second
    ISLIKE = arg Like pattern
    End Function



    Returns TRUE if cell A1 contains a vowel (upper or lower case)

  2. tuxedobuford

    Hi, I’d like to use a cell reference instead of a defined string but can’t seem to get the syntax correct when combining the wildcard with the cell reference. Any ideas?


  3. tuxedobuford

    Found it..COUNTIF(RANGE,”*” & CELL & “*”)
    whereas RANGE is my search range and CELL contains the string I’m counting.


  4. Pete

    Hi, Mike

    For a worksheet formula, something like

    (Of course IN is available in SQL within Excel.)

    regards, Pete

  5. Camille

    In Excel 2010, I have the formula below that works as long as the the data is an exact match but with the names in columns G and X there are often trailing spaces or misspellings. Is there a way to use a Like or wildcard function to pull something like “MC CH*” that would give me anything starting with MC CH??

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(A34:A6000,ROW(A34:A6000)-ROW(A$34),0,1)),–(G34:G6000= “McArthur Jr., Melvin”),–(T34:T6000=3),–(X34:X6000=”MC CHESNEY “),–(N34:N6000=”OPEN”))

  6. Jeff Weir

    Camille – you might want to check out Excels DSUM function. Excel has a whole bunch of Database functions called DSUM, DMAX, etc that filter a table based on criteria, and return an aggregated total. You can do very complex filtering indeed including non-additive filtering, advanced formulas within the criteria etc.

    You might also know that you can use Excel’s “datatables” functionality to ‘swap’ up to 2 inputs of a formula with a designated range of values, so you can see what difference a change in those two input parameters makes to your model output.

    I didn’t think that either function on their own was that much of a deal until I stumbled across this great article at about combining them. I had not realised until I read the article that you can use datatables to ‘swap out’ the criteria in a DSUM or any other database function. Very very powerful indeed. So you can set up amazing filtering run directly off the spreadsheet in a way that users can easily interact with, and do stuff that would otherwise require SQL. Blows pivots out of the water, on account that pivots only allow additive filtering.

    Hui has a great article on DataTables here which is amazing. See the section where he talks about multi input (more than 2) data tables. Hat tip to Sam for that link.

Leave a Reply

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

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    Markdown is turned off in code blocks:
     [This is not a link](

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see