Forum Discussion

Cmoreno89's avatar
Cmoreno89
Brass Contributor
Aug 13, 2023

Formula for .. IF Partial Match = PASTE, IF NO MATCH = BLANK

Hello everyone,   I'm looking for a partial match formula.   I'm looking to partial match cell $D5 in my excel spreadsheet with $K$28:$K$400.   $D5 must be a PARTIAL MATCH to ANY of the cells i...
excel
Excel on mobile
Formulas and Functions
office 365
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 15, 2023

Cmoreno89 

Formula exists, that's COUNTIF on "*" & range & "*". If we exclude dynamic array functions which could not work on mobile. Plus that's the difference between versions on Android and iOS. COUNTIF shall work on any version.

The question is mainly in logic of patterns for filtering.

Cmoreno89's avatar
Cmoreno89
Brass Contributor
Aug 15, 2023
Well, what would the formula be then with COUNTIF? I'm on an android tablet version of excel with the most up-to-date version from the play store.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 16, 2023

    Cmoreno89 

    Unfortunately I have no Android tablet around and not sure which functionality is available on it. Also you didn't confirm formula for the birthday in one of previous posts works on it. Thus let use formulae which shall work on any Excel.

    By the way, I'm not sure you are in US locale or not, other words "My Birthday: 8/11/1993" means Aug 11or Nov 08. In formula is the latest.

    Back to filter. I'd suggest to add two helper cells, for before and after. They could be at any place of the workbook, here

    they are in C7 and C8. With that formula in E5 could be

    =IF( OR(
    ISNUMBER( MATCH($C$7,$E$9:$E$200, 0) ),
    ISNUMBER( MATCH(" " & $C$7 & "/",$E$9:$E$200, 0) ),
    ISNUMBER( MATCH("/" & $C$8 & ",",$E$9:$E$200, 0) ),
    ISNUMBER( MATCH($C$8,$E$9:$E$200, 0) )
    ),
    $D$5,
    ""
)

    You may add any other patterns for filtering. In above we assume full number itself, or number between space and slash; or number between slash and comma.

Resources