How easely prevent problems with MATCH on a array with one value

If I try the match function on an array of 1 value, Excel returns always #N/A. Why?
If you match on a range of 1 cell Excel does well.

This is very annoying when needed in extended formulas (when you don't know the size of the array before).
I did come up with a workaround for this by adding a dummy value behind the array with VSTACK,
but I don't find that elegant at all.

Does anyone know a better solution?
See attachment with working and not working examples and my workaround solution.

Does anyone have better ideas?

Not an answer, but I have got round it like this before:

=INDEX(array,1)

Don't know why it happens.

=Index(arrray,1)?

This reruns always the first value of the array, but that is not what I want.

If the array contains 1 value it should return 1 or #N/A. If the array contains 2 values it should return 1, 2 or #N/A etcetera.

You said an array of one value returns N/A with MATCH. I have found the same, but indexing the array first as I showed gets round it.

I think you need to give a more detailed example of what you mean.

If an array contains 1 value the formula =MATCH(value, array) returns always #N/A.

What I want a formula that in case of 1 value returns 1 or #N/A and in case of 2 values returns 1, 2 or #N/A and so on.
I need this in extended formulas.

I would expect the formula =MATCH(value, array) to do that. But it does not do that if the array consists of 1 value.
As far I can tell, The MATCH formula works only correct if an array have at least 2 values.
I find that very strange, because the formula works well on a range of 1 cell.

The formula =MATCH(value,VSTACK(array,"") does the job correct if the array has only 1 value, but I think that's a crazy solution.

CrosspostingI also tried posting this question at https://techcommunity.microsoft.com/.

I thought that went well, but now I can't find my own post.
I don't know if I did something wrong when posting or when looking back.
Pff.

For what it’s worth, you don't need to stack the null value. This works for me:

Formula:

Please Login or Register  to view this content.

Slightly more aesthetically pleasing, I guess, and you can drag it down.

1 =MATCH(6,VSTACK(A$2:A2*2))
2 =MATCH(6,VSTACK(A$2:A3*2))
3 =MATCH(6,VSTACK(A$2:A4*2))

You only appear to have one post on that forum: https://techcommunity.microsoft.com/...ser-id/1427699

Thanks Trevor,
That's a lot better than my solution. Despite the use of VSTACK, there is no stacked value, so a wrong result can never arise.

I keep finding it very strange that I also have to use VSTACK with the match function on an array, when the array can consist of 1 value.

The fact that that is necessary is a bug in the MATCH function for me:
I can't find anywhere that an array of 1 value should not be allowed as the second parameter in a MATCH function.

Problem is solved for me.

I've looked at your attachment and it's a bit different (but similar) to an issue I found. INDEX won't work in your case, but it did on mine (but I can't find the problem I was working on when it happened).

@Hans: You're welcome. Thanks for the rep.

Try XMATCH...

=XMATCH(6,2*A$2:A2,0)

reurns 1 in all 3 instances, but ONLY with an exact match. If you want the ascending series of answers (1,2,3), use:

=XMATCH(6,2*A$2:A2,1,-1)

I'm getting forgetful... for NUMBERS you can also use =MATCH(6,2*A$2:A2+{0})

and for TEXT +{""}

@Ali, thanks for looking.

@Glenn,

Yes XMATCH does the job, exactly as you expect it . You just have to know. Thanks.

I will keep the other option of post #13 in mind for use in Excel versions before 2021 (beacause there is no XMATCH and there is no VSTACK).