+ 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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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)

Similar Threads

  1. [SOLVED] Excel Macro - Copy & Paste (Font) problem & Delete Last Added Rows problem
    By LennartB in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-05-2015, 06:58 AM
  2. Replies: 1
    Last Post: 04-01-2015, 02:30 PM
  3. Replies: 6
    Last Post: 10-20-2013, 07:16 PM
  4. input box problem, cell filtering problem, result display while locking sheet
    By croozin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-01-2013, 08:52 AM
  5. Replies: 6
    Last Post: 05-28-2013, 05:08 PM
  6. Replies: 2
    Last Post: 01-22-2013, 07:09 AM
  7. Problem using formulas within macro + problem with defined name.
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-11-2011, 04:39 AM

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