Worksheet Formula: Find value in an array

  • I've got a question about finding a value in an array. The following Table is called CustomerSubscription.


    I need to be able to tell, based on a CustomerID, whether he/she still has an active subscription. If yes,
    the value should be true, if not then it should obviously be false.


    For example, customer 1 has an active subscription - whereas customer 2 has none.


    What is the best way to solve this problem. I have no problems if each customer only has one subscription,
    but I'm unfamiliar on how to do this with arrays - that's what I have to use, right?


    I've attached the worksheet to this post.
    forum.ozgrid.com/index.php?attachment/66523/


    Thanks for your help.


    Massimo




    ABCDEF
    1CustomerIDSubscription StartLengthSubscription EndActiveRemaining
    2101.01.201236531.12.2012FALSE-937
    3101.06.20159030.08.2015TRUE23
    4201.05.201336501.05.2014FALSE-456
    5201.01.201021030.07.2010FALSE-1807
    6201.01.20151011.01.2015FALSE-206
    7401.08.201136531.07.2012FALSE-1087
    8401.08.201540004.09.2016TRUE387
    9401.06.201290018.11.2014FALSE-259
  • Re: Worksheet Formula: Find value in an array


    Not too sure exactly what you want, where the formula is to go, etc...


    For example, in Col G, Row 2:


    [bfn]=IF(COUNTIFS([CustomerID], [@CustomerID], [Active], TRUE), "Active", "")[/bfn]


    Your table will extend to incorporate Column G.


    The formula can be simply copied to existing rows and will automatically be included in new rows.

  • Re: Worksheet Formula: Find value in an array


    I did indeed forget to mention where the result should be. Apologies for that.


    I just want to be able to check whether a customer has (from the many subscriptions)
    at least an active one. The result should be in B14, B15, B16 I hope that it's a bit clearer now.




    ABCDEF
    1CustomerIDSubscription StartLengthSubscription EndActiveRemaining
    2101.01.201236531.12.2012FALSE-937
    3101.06.20159030.08.2015TRUE23
    4201.05.201336501.05.2014FALSE-456
    5201.01.201021030.07.2010FALSE-1807
    6201.01.20151011.01.2015FALSE-206
    7401.08.201136531.07.2012FALSE-1087
    8401.08.201540004.09.2016TRUE387
    9401.06.201290018.11.2014FALSE-259
    10
    11
    12
    13CustomerIDHas active subscriptions
    141TRUE / FALSE
    152TRUE / FALSE
    164TRUE / FALSE

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!