# 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/

Massimo

 A B C D E F 1 CustomerID Subscription Start Length Subscription End Active Remaining 2 1 01.01.2012 365 31.12.2012 FALSE -937 3 1 01.06.2015 90 30.08.2015 TRUE 23 4 2 01.05.2013 365 01.05.2014 FALSE -456 5 2 01.01.2010 210 30.07.2010 FALSE -1807 6 2 01.01.2015 10 11.01.2015 FALSE -206 7 4 01.08.2011 365 31.07.2012 FALSE -1087 8 4 01.08.2015 400 04.09.2016 TRUE 387 9 4 01.06.2012 900 18.11.2014 FALSE -259

## Files

• 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.

 A B C D E F 1 CustomerID Subscription Start Length Subscription End Active Remaining 2 1 01.01.2012 365 31.12.2012 FALSE -937 3 1 01.06.2015 90 30.08.2015 TRUE 23 4 2 01.05.2013 365 01.05.2014 FALSE -456 5 2 01.01.2010 210 30.07.2010 FALSE -1807 6 2 01.01.2015 10 11.01.2015 FALSE -206 7 4 01.08.2011 365 31.07.2012 FALSE -1087 8 4 01.08.2015 400 04.09.2016 TRUE 387 9 4 01.06.2012 900 18.11.2014 FALSE -259 10 11 12 13 CustomerID Has active subscriptions 14 1 TRUE / FALSE 15 2 TRUE / FALSE 16 4 TRUE / FALSE
• Re: Worksheet Formula: Find value in an array

A simple edit to the formula...

[bfn]=IF(COUNTIFS(Tabelle2[CustomerID], A14, Tabelle2[Active], TRUE), TRUE, FALSE)[/bfn]

• Re: Worksheet Formula: Find value in an array

Oh it's so easy! Thanks for the formula.
And now I've learned something new.

Thanks

## Participate now!

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