Can some expert help in MATCH() array form?

[4world]

This seems to be an Excel 2013 bug ...

	A	B
1	something	{=MATCH(FALSE, ISBLANK(A1:A1), 0)}
2

<tbody>
</tbody>

(Use Ctrl-Shift ENTER to enter the B1 formula after typing without the curly braces)

B1 should generate 1 whereas it returns #N/A.

Change the range to A1:A2 and it works fine. The array function of MATCH() is the culprit (not the ISBLANK() since the same behavior is seen if ISBLANK() is replaced with some other function).

The above has been simplified from a more complex formula that uses a dynamic range generated by OFFSET() which fails when the range changes to one cell.

Any help would be much appreciated...

 

[Rick Rothstein]

If you look up the help file for the MATCH function, it Desciption says...

The MATCH function searches for a specified item in a range of cells...

The word "range" is blue indicating it is a hyperlink, clicking it shows this definition for "range"...

range: Two or more cells on a sheet. The cells in range can be adjacent or nonadjacent.

Since A1:A1 is a single cell reference, the single cell argument to the ISBLANK function does not meet the definition of a "range" and, hence, cannot be used as the argument for the MATCH function.

 

[Aladin Akyurek]

MATCH is not fed with an array, i.e. ISBLANK fails to/does not return an array. All what it gets is just "something", a scalar value. Hence the failure. [ MATCH doesn't work with a single cell, often thought of as a single element range, either. ]

If you can force an array return from the ISBLANK evaluation, MATCH will behave accordingly"

=MATCH(FALSE, ISBLANK(CHOOSE({1},$A$1:A1)), 0)

 

[Jon von der Heyden]

Cross-posted here: Strange behavior by MATCH() in array form -- possible Excel bug?

For future reference, if you are going to cross-post, please make it known to us and please provide a reference back to your cross-post so that possible participants can see what has and has not already been suggested.

 

[4world]

MATCH is not fed with an array, i.e. ISBLANK fails to/does not return an array. All what it gets is just "something", a scalar value. Hence the failure. [ MATCH doesn't work with a single cell, often thought of as a single element range, either. ]

If you can force an array return from the ISBLANK evaluation, MATCH will behave accordingly"

=MATCH(FALSE, ISBLANK(CHOOSE({1},$A$1:A1)), 0)

Aladin:

Absolutely brilliant!!! I had also observed under "Formulas->Evaluate Formula" that ISBLANK was not returning an array when the range came down to 1 cell but could not go to this level. Your post has advanced my level a bit -- thanks a ton.

I was really at a dead-end since I had to use the MATCH function yet it wasn't working as expected.

I am amazed at the number of posts you (and Rick) have, wondering what keeps you guys so motivated to selflessly help others. You are making the world a better place by sharing your knowledge and my humble bow to you all.

Rick:

Thanks for your answer and time.

Since A1:A1 is a single cell reference, the single cell argument to the ISBLANK function does not meet the definition of a "range" and, hence, cannot be used as the argument for the MATCH function.

Just FYI the above is wrong:
1. ISBLANK is actually meant for a single cell yet it can take an array when entered with Ctrl-Shift-Enter.

2. ISBLANK should return an array when used in array form so it does work fine as an argument in MATCH(). It is possibly a bug in ISBLANK (and other similar functions) that fail to return an array when the array has only one element.


Jon von der Heyden:

Thanks for the advice which is noted for future. I had posted the first question yesterday and 12-14 hours later, it had only one useless response, so I re-drafted the question here, and it got a brilliant answer.

 

[Aladin Akyurek]

@4world

You are welcome. I guess helping becomes second nature to some folks.

 

[4world]

@4world
... helping becomes second nature to some folks.

Hope this happens to more folks as it's desperately needed in the world.

May I ask another question since your formula did solve a puzzle but my application still cannot use it.

I need the formula to return the reference (vertical offset) of the first non-blank cell in a column where the column range is dynamically generated (i.e. it can be more than one cell or just one cell). So:

	A	B
1		<formula here=""></formula>Formula here
2	something

<tbody>
</tbody>

B1 should return 2 if the range evaluates to A1:A2
B1 should return 1 if the range evaluates to A2:A2 (by the use of OFFSET())

Thank you again.

With best wishes.

 

[4world]

I think this might be the answer:

=MATCH(FALSE, CHOOSE({1}, ISBLANK(A1:$A$2)), 0)

Seems to work

 

[Aladin Akyurek]

I think this might be the answer:

=MATCH(FALSE, CHOOSE({1}, ISBLANK(A1:$A$2)), 0)

Seems to work

Confirmed with control+shift+enter and copied down, this would return 2 and 1 because of:

CHOOSE({1},A1:$A$2) >> {TRUE;FALSE}

&

CHOOSE({1},A2:$A$2) >> {FALSE}