Array formula with MATCH and IF gives incorrect results

[abrguyt]

(see text in next post)

[abrguyt]

Using the array formula: {=MATCH(B3, IF(TRUE, B5:B8))} gives incorrect index results, assuming B3 contains the lookup key value, and the B5:B8 range the values in which to lookup the key. The result using EPPlus v. 4.5.3.1 seems to be always 1, instead of the actual row index for the matching value in the lookup range.

Using Match without the array formula mode gives the correct index, so maybe the problem is in the IF function not being able to work correctly in array mode.

Please find attached the .xlsx file; reading the value of B2 using EPPlus will yield the incorrect value (1), while Excel gives (3) as correct value. epplus test arrayformula.xlsx

[swmal]

Hi @abrguyt,

The formula calc engine in EPPlus does not yet support array formulas. We plan to implement it in EPPlus 5. It is mentioned at the bottom of this wiki page, but I do understand if you haven't seen it. We will make it clearer in future documentation.

/Mats

[abrguyt]

Hi @swmal,

Thanks for letting me know - I hadn't seen that on the wiki page indeed. Now looking forward to the version 5 release !! :-) That will be a great leap forward.

[swmal]

Many thanks, just to set the expectations straight - it will not be in the initial EPPlus 5 release, but when we have stabilized the new version array formulas are one of the things we will start implementing.