Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

Make a function act as an array function inside a non-array formula.

This would be for any function that could, logically, act in an array manner.

If one typed the function using {} instead of (), it would do so while behaving "normally" (= as it does now) if using (). For example:

VLOOKUP{} vs. VLOOKUP()

The latter would act as expected today, producing a single result no matter the possibilities (an error still being a result).

The former would produce an array of all the potential results (now it finds the first match and acts on it... this would find EVERY match, act upon each, and produce an array populated by the results).

Functions that examine a range, for example, now return the contents of the upper leftmost cell EVEN THOUGH usin F9 to evaluate chunks of the formula shows Excel is internally producing an array of all the contents in the range, then selecting the first one. This would override that giving you the array to process as you wish.

All in an overall non-array formula. Only the function itself would return an array.

A corresponding idea would be a function that would turn an array with a single element into a non-array. Some functions break if acting upon an array, even a single element array, while they can handle values just fine. So FUNCTION({9}) (whatever it was wrapped around produced "{9}" not "9") breaks an entire formula giving an error while FUNCTION(9) would have let the formula work just fine. And since it is internal to the Excel calculation, you can't wrap the inside function with CHOOSE()... that returns an error as well. Broken either way even though Excel clearly already has the code present to de-array an internal step, hence the functions operating on ranges having the range's first element (upper leftmost cell contents) returned. It just needs to be accessible to users. Two ideas for one! Really though, same coin.

4 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base