Hi All:

Say I have the following array:

Product Salesperson Apr May Jun Jul Aug
X100 J. Smith 2 4 3 5 6
X100 S. Jones 2 3 4 3 4
X100 T. Fields 1 3 2 4 5
X100 F. Tucker 3 4 4 5 6
X200 J. Smith 1 3 2 4 5
X200 S. Jones 3 4 4 5 6
X200 F. Tucker 1 3 2 4 5

I want to use a function that returns all occurrences of June data for
product X100 for all salespeople. That is, I want to return the following:

Product Salesperson Jun
X100 J. Smith 3
X100 S. Jones 4
X100 T. Fields 2
X100 F. Tucker 4

Can anyone suggest a method of doing this? Note that the array heights can
vary as not all salespeople will forecast on a specific product.

Thanks.

Pivot table is the best option.

http://www.contextures.com/excelfiles.html#Pivot


"Umbu" wrote:

Hi All:

Say I have the following array:

Product Salesperson Apr May Jun Jul Aug
X100 J. Smith 2 4 3 5 6
X100 S. Jones 2 3 4 3 4
X100 T. Fields 1 3 2 4 5
X100 F. Tucker 3 4 4 5 6
X200 J. Smith 1 3 2 4 5
X200 S. Jones 3 4 4 5 6
X200 F. Tucker 1 3 2 4 5

I want to use a function that returns all occurrences of June data for
product X100 for all salespeople. That is, I want to return the following:

Product Salesperson Jun
X100 J. Smith 3
X100 S. Jones 4
X100 T. Fields 2
X100 F. Tucker 4

Can anyone suggest a method of doing this? Note that the array heights can
vary as not all salespeople will forecast on a specific product.

Thanks.