Is there a workaround for Excel functions that will not take the result of VSTACK() as an argument?

[TomCon]

For instance, RANK.EQ will not take the result of VSTACK() as an argument.

=RANK.EQ(5,VSTACK(4,5,6))
is not accepted as a formula, Excel gives the generic error message "There's a problem with this formula".
Also this does not work (with braces)
=RANK.EQ(5,VSTACK{5,6,7})

Is there a way that i can enumerate an argument that is not a range to RANK.EQ?

(I have also found functions other than RANK.EQ that have this behavior of not accepting the result of HSTACK or VSTACK as an argument, though i do not recall which ones at the moment. In most cases one can use HSTACK() or VSTACK() as an argument to an Excel function that would normally accept a range).

Thank you if you can help out for a workaround for this.

 

[Fluff]

It has nothing to do with hstack or vstack. Rank does not accept an array for the 2nd argument, it has to be a range.
Another option is something like

=XMATCH(5,SORT(VSTACK(7,5,6)))

 

[TomCon]

It has nothing to do with hstack or vstack. Rank does not accept an array for the 2nd argument, it has to be a range.
Another option is something like

=XMATCH(5,SORT(VSTACK(7,5,6)))

Thanks for this workaround. It works!...did have to modify search order with larger array sizes (my simplified example works as presented, but for me the sort needed to have -1 added, as:
SORT(VSTACK(),,-1)
for example.

I wanted to add a possible additional question but perhaps there is no answer. It is confusing to me that some Excel functions apparently specifically require a range argument (as RANK.EQ does, apparently), while many others that one normally uses with range arguments, do not absolutely require the argument to be a range, but it usually is. I'm not sure how to tell which ones requrie a range as vs. the ones where one almsot always uses a range, but specifically a range is not required. Just about all in most common use, SUM, MIN, MAX, and so on, you usually use with a range as an argument, but they work fine with VSTACK() or HSTACK().

Is there any general way to make VSTACK() or HSTACK() return a result that can be used in Excel functions that REQUIRE a range argument? Or must one always search for a "clever workaround" that is specific to the function in question? Which is what i would call this solution, a "clever workaround that employes XMATCH and SORT to attain the desired result that mimics RANK.EQ".

Thanks!

 

[Fluff]

Is there any general way to make VSTACK() or HSTACK() return a result that can be used in Excel functions that REQUIRE a range argument?

No, both those functions return an array.