Match Array Formula

[brkkrkmz112]

Hi everybody,


Today I noticed that something and I really wondered the reason of it.

Normally when I use Match formula for the match type generally I am just selecting exact match from the popup and never seen any problem. Today I was trying to find first blank cell in a range so wrote this array formula " {=+MATCH(TRUE;ISBLANK(Q6:AC6))} "


It is not working when ı just select exact match from pop up and ctrl+shift enter. But when I manually put zero to match type its working.

Could you explain the reason of it.Thanks in advance.

 

[Special-K99]

This seems to work

=MATCH(1,IF(LEN(A1:F1)=0,A1:F1))
Array formula, use Ctrl-Shift-Enter

 

[steve the fish]

You are trying to find the exact match of TRUE within a array of TRUE/FALSE results based on the ISBLANK test so you need the exact match.

 

[brkkrkmz112]

Yes but what I could not understand is that what is the difference between manually putting zero and selecting zero from pop up window. Why one of them is working, other is not.

 

[steve the fish]

Cant say as i understand what you mean. Your formula above doesnt include a zero so defaults to 1 so produces an incorrect result.

 

[XOR LX]

You have to double-click your choice of parameter from the pop-up. Single-clicking won't suffice.

Regards

 

[brkkrkmz112]

What I understand is when you click the cell 0 should be seen in the formula bar. Otherwise, it is not working properly. As you said when you clicked once, 0 is not seen in the formula but it is working for non-array formulas. In my understanding it is happining by chance.

Thank you for your kind assistance.