+ Reply to Thread
Results 1 to 4 of 4

Look up problem

  1. #1
    Registered User
    Join Date
    03-18-2009
    Location
    Solihull
    MS-Off Ver
    Excel 2003
    Posts
    53

    Look up problem

    Hi - I have a table extracted below which repeats many codes as months are actualised for sales
    I have a summary sheet and would like to highlight the 1st month a sales occurs for each item - unfortunately I cannot use just the 1st month it appears in the report as sometimes we don't initially have sales
    So below would be ARTSQETCH Jul-17 and ARTGLSLANT would be May-16 - Is there a calc I could use ? thanks

    Code Month Sales
    ARTSQETCH May-17
    ARTSQETCH Jun-17
    ARTSQETCH Jul-17 500
    ARTGLSLANT Feb-16
    ARTGLSLANT Mar-16
    ARTGLSLANT Apr-16
    ARTGLSLANT May-16 50
    ARTGLSLANT Jun-16 100
    ARTGLSLANT Jul-16 150
    ARTGLSLANT Aug-16 200
    ARTGLSLANT Sep-16 250
    ARTGLSLANT Oct-16 250

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    35,395

    Re: Look up problem

    Try this:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    1
    Code Month Sales
    2
    ARTSQETCH
    May-17
    ARTSQETCH
    01/07/2017
    3
    ARTSQETCH
    Jun-17
    ARTGLSLANT
    01/05/2016
    4
    ARTSQETCH
    Jul-17
    500
    5
    ARTGLSLANT
    Feb-16
    6
    ARTGLSLANT
    Mar-16
    7
    ARTGLSLANT
    Apr-16
    8
    ARTGLSLANT
    May-16
    50
    9
    ARTGLSLANT
    Jun-16
    100
    10
    ARTGLSLANT
    Jul-16
    150
    11
    ARTGLSLANT
    Aug-16
    200
    12
    ARTGLSLANT
    Sep-16
    250
    13
    ARTGLSLANT
    Oct-16
    250
    Sheet: Sheet1

    Excel 2016 (Windows) 32 bit
    F
    2
    =MIN(IF($A$2:$A$13=E2,IF($C$2:$C$13<>"",$B$2:$B$13)))
    Sheet: Sheet1

    ... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    03-18-2009
    Location
    Solihull
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Look up problem

    that's great thanks

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    35,395

    Re: Look up problem

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1