Share via

EXCEL: need a new function "between"

Anonymous
2022-08-19T19:54:39+00:00

I want to recommend a new function "BETWEEN"

look in a list (column) of numbers, and find all numbers between xx and yy, and returns a new list [vstack or hstack) of all names that meet the criteria

formula in cell F1 =BETWEEN(B:B, C2, D2, 1) where 1 means to sort, or 0 means to not sort

formula in cell G1 =BETWEEN(B:B, C2, D2, 0)

A B C D E F G
1 tom 1.9 mary mary
2 mary 2.0 2 4 jack sue
3 sue 3.9 sue jack
4 joe 4.3
5 jack 2.7
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2022-08-20T08:54:14+00:00

    =SORT( FILTER(A:A,(B:B>=C2)*(B:B<=D2),"no match"),,1)

    =FILTER(A:A,(B:B>=C2)*(B:B<=D2),"no match")

    Sample file
    https://1drv.ms/x/s!AsEpmlJLteasjXSWRGgJNpZNpSgd?e=4PhZJy

    Andreas.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-08-20T07:45:23+00:00

    Hi there

    I like your suggestion and I totally agree with Nothing LTL.

    Nevertheless,

    If the output you of that new between formula would like the one in column F or G then means you want to filter the data in column A by the criteria in columns C and D.

    If so,

    With no intention to deter you from getting a new formula goals

    I humbly suggest you try the following

    Formula option

    The relative new FILTER and SORT functions might do the job.

    Also a combination of the AGGREGATE AND INDEX functions for Excel desktop versions will give you similar output.

    Built in Features option

    A Pivot Table

    And ultimately Power Query

    Will give you the solution you want.

    Regards

    Jeovany

    Was this answer helpful?

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. HansV 462.6K Reputation points
    2022-08-19T21:00:13+00:00

    You can submit a suggestion from within Excel using File > Feedback > I have a suggestion.

    Alternatively, post it on the Excel Feedback Portal

    Was this answer helpful?

    0 comments
  5. Anonymous
    2022-08-19T21:00:10+00:00

    Re: "I want to recommend a new function 'BETWEEN' "

    I like your suggestion; but I carry no weight with MS.

    The function will need another argument specifying the range that contains the data to return.

    There is a User Voice link (i don't have it handy) where you can send requests.
    From what I gather, most requests die there.

    Good luck with it.

    '---

    Nothing Left to Lose

    Was this answer helpful?

    0 comments