Search code examples
google-sheets-formulamatchvlookuparray-formulaspartial-matches

Google Sheets ARRAYFORMULA with Partial Match and Multiple Criteria Not Returning Expected Results

I’m working with a Google Sheets document where I need to look up values based on multiple criteria and perform a partial match search in one of the columns. I’ve been trying to use an ARRAYFORMULA combined with functions like IF, ISNUMBER, SEARCH, INDEX, and MATCH, but I’m not getting the expected results.

enter image description here

  1. I have a main data range from columns A to D.

    1. Column A: “RE Number” (contains unique identifiers like R1001, R1002, etc.)

    2. Column B: “Order ID”(contains codes like W-02TTNO, W-02T7M7, etc.)

    3. Column C: “Seller” (contains names or codes that may have multiple values or combinations, e.g., “FKL & RMU”)

    4. Column D: “Date” (contains dates)

  2. I have search criteria in another range from columns F to H.

    1. Column F: “RE Number” (search criteria)

    2. Column G: “Order Nr” (search criteria)

    3. Column H: “Seller” (search criteria for partial match)

  3. I want to search through the data range (A:D) and find a row where:

    1. Column A matches the value in Column F.

    2. Column B matches the value in Column G.

    3. Column C contains (partial match) the value in Column H.

    4. If all conditions are met, it should return the corresponding date from Column D.

If I use formulas without ArrayFormula, it is working, but I'd like to find a solution with ArrayFormula.

I tried using this formula at the end:

=ARRAYFORMULA(IF(F2:F <> ""; IFERROR(INDEX(D:D; MATCH(1; (A:A = F2:F) * (B:B = G2:G) * ISNUMBER(SEARCH(H2:H; C:C)); 0)); "No Result"); ""))

This formula is supposed to:

  • Check for each row in Column F if it’s not empty.

  • Use MATCH to find the first row in the data range where all conditions are true

  • Return the corresponding date from Column D

  • Handle errors by returning “No Result” if no match is found

However, the formula always returns "No Result", even though there are rows in the data that should match the criteria.

The Seller column (C) can contain multiple values separated by an “ & “ (e.g., “FKL & RMU”), and I want to find a match if any part of the cell matches the value in Column H (e.g., “RMU”). • I’m using Google Sheets and would prefer a solution that works with its functions and limitations. Any help would be greatly appreciated!

Solution

  • Alternate way for Arrayformula

    Arrayformula does not work with your current formula. Please try this instead.

    Sample Formula:

    =MAP(F2:F26,G2:G26,H2:H26, LAMBDA(x,y,z, IF(ISBLANK(x)," ",IFERROR(CHOOSECOLS(FILTER(A2:D , A2:A=x,B2:B=y,REGEXMATCH(C2:C,z)=TRUE),4), "No Result"))))

    Sample Output:

    Sample Output

    Note: Please feel free to comment if you have any questions on how to implement this on your end.

    Reference:

    MAP

    CHOOSECOLS