ARRAYFORMULA from Google Sheets not working in Excel

Question

I have an ARRAYFORMULA with regular expression matching in Google Sheets that I need to convert to Excel.

The working Sheets formula is:

=ARRAYFORMULA(IF(regexmatch(E2:E,"Call"),100*(I2:I*D2:D),""))

But I'm not having any luck debugging it in Excel.

The formula checks column E to see if the cell for row x contains the text "call". If it does, then it multiplies the value in the column(I) cell by the value in the column(D) cell.

EDIT

L2 has the formula: =IF(ISNUMBER(FIND("Call",E2:E10)),100*D2:D10*I2:I10,"")

Also tried this: =IF(E2:E8="Call",100*L3D2:D8*I2:I8,"")

Both return the spill error and I don't know how many rows I'll have so the formula wouldn't work anyway.

Answer 1

Fn1 - Regex equivalent (padding, case sensitive filter)

=IF(ISNUMBER(FIND("Call",B2:B8)),100*D2:D8*E2:E8,"")

This provides for the following per the Regex function:

1. Case preservation (i.e. "A" <> "a" etc.)
2. Padding allowed (i.e. "12-Call", "Callxyz" etc. will match too)

Revision: see here for sample file (which is clearly 'working' per screenshot[s] below - noting any 'spill' errors are due to something below the function that is getting in the way - only enter the function in a single cell - the other cells will automatically populate - this is an array function) - If you are not using Excel 365, then use this function, and include it in the table:

=IF(ISNUMBER(FIND("Call",B2)),100*C2*D2,"")

Likely that you are using 365 Office compatible version of Excel though (because older versions did not have "SPILL!" feature - in which case you can simply enter the following funciton in a single cell and hit enter! ☺

=IF(ISNUMBER(FIND("Call",B2:B7)),100*C2:C7*D2:D7,"")

The remainder of my solution (which is virtually identical to above, minus the table entry option) now resumes:

---

Fn2 - Alternative * FYI (strict equality)*

Whilst the scope of this Q does not encompass variations - I provide 1 simple example for the sake of interest. This differs from the function above by placing the additional restriction of 'strict equality' (i.e. no 'padding')...

=IF(B2:B8="Call",100*D2:D8*E2:E8,"")

---

Final comments

RE: equivalence between Sheets and Microsoft Excel versions of this function..

I believe the ranges E2:E, I2:I are dynamic (I may be mistaken), but if not, here are a couple of alternative ways to achieve the same in Excel:

=LET(x_, MAX(FILTER(SEQUENCE(ROWS(E:E)),--(E:E<>"")))-2, E2:OFFSET(E2,x_,0))

• This will take the dynamic range from E2 to the final non-blank cell in that column
• A common popular alternative is the simpler approach: E2:offset(E2, counta(E2:E100) -1,0)
• Whilst simpler, it is vulnerable to blank cells that fall within the data
• That said, the former (see screenshot) requires Office 365-compatible Excel

If dynamic ranges are important - you could also suing Tables in Excel) i.e. navigate to: (File | Options | Formulas | 'Use Table Names in formulas if this was desired)