Results 1 to 3 of 3

Thread: Help with formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1

    Join Date
    Sep 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with formula

    Hi, i have a range of sales starting in column Q2 and I need to add a formula that will add a value based on the range of the sale. Say Q3 has a sale value of 43.00 I would want this to return Group A because its within the 0-50 range of sales for first column. The complete list would be:

    Group A: 0-50
    Group B: 51-99
    Group C:100-199
    Group D: 200-499
    Group E:500-999
    Group F: 1000-1999
    Group G: 2000 and above

    Thanks so much for any help.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,041
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Help with formula

    Welcome to the Board!

    You can use VLOOKUP to do this. Just make a two column lookup table, where the first column is the LOWER limit of each range (0, 51, 100, ...) and the second column is the Group Name you want returned. Let's say that range is A1:B7, and the value we want to look up (43) in is cell C1.

    Then, we could use a VLOOKUP formula like this:
    Code:
    =VLOOKUP(C1,$A$1:$B$7,2,TRUE)
    Here is a write-up with more detail on this method: https://www.howtogeek.com/406053/how...nge-of-values/
    Last edited by Joe4; Sep 26th, 2019 at 04:41 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,170
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Help with formula

    How about
    ="GROUP "&LOOKUP(Q3,{0,51,100,200,500,1000,2000},{"A","B","C","D","E","F","G"})
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •