Microsoft official help about the array formula: https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7?ui=en-US&rs=en-US&ad=US&fromAR=1

steps to reproduce

1. load the attached excel file
2. see if!D11

current result

if!D11 is #VALUE! because it's not in the row or column range of its area reference A2:A4

expected result

D11 can be evaluated to a correct result: yes

extra info

1. the sheet "array formula" contains an example from the official document (F12:F21)