Return value from 1st Column based on Data found in corresponding Columns

1. Return value from 1st Column based on Data found in corresponding Columns

Hi, I've been using this forum to solve multiple challenging problems for some time , but I recently signed up to ask a question. Hopefully, I can get some assistance.

Based on a date range that are a table column's names (column names are formatted as dates), I would like to return the values from the first column that are based on the values within the table.

For example, based on the table below, for values that are 20% and less, between 11/1/2013 and 1/1/2014, I would like to return "Person A, Person C, Person D and Person F"

TABLE

----------------------------------------------------
| Resources| 09/13|10/13|11/13|12/13|01/14|02/14|
----------------------------------------------------
|Person A | 0% | 30%|20%|10%|10%|20%|
|Person B | 0% | 30%|20%|40%|40%|20%|
|Person C | 0% | 30%|20%|10%|10%|10%|
|Person D | 0% | 30%|20%|10%|10%|20%|
|Person E | 0% | 30%|20%|40%|40%|20%|
|Person F | 0% | 30%|20%|10%|10%|10%|
------------------------------------------
RESULT

----------
| Resources|
----------
| Person A |
| Person C |
| Person D |
| Person F |
----------

Thank You!!!!

2. Re: Return value from 1st Column based on Data found in corresponding Columns

Can you use a helper column?

Data Range
 A B C D E F G H 1 Resources 9/13 10/13 11/13 12/13 1/14 2/14 T/F 2 Person A 0% 30% 20% 10% 10% 20% TRUE 3 Person B 0% 30% 20% 40% 40% 20% FALSE 4 Person C 0% 30% 20% 10% 10% 10% TRUE 5 Person D 0% 30% 20% 10% 10% 20% TRUE 6 Person E 0% 30% 20% 40% 40% 20% FALSE 7 Person F 0% 30% 20% 10% 10% 10% TRUE 8 9 From To 10 Date Range 1/14 2/14 11 12 Person A 13 Person C 14 Person D 15 Person F

B1:G1 = 1st of the month dates formatted to display as m/yy

B10:C10 = 1st of the month dates formatted to display as m/yy that define the date range

Enter this array formula** in H2 and copy down as needed:

=AND(INDEX(B2:G2,0,MATCH(B\$10,B\$1:G\$1,0)):INDEX(B2:G2,0,MATCH(C\$10,B\$1:G\$1,0))<=0.2)

Enter this array formula** in A12:

=IFERROR(INDEX(A\$2:A\$7,SMALL(IF(H\$2:H\$7,ROW(H\$2:H\$7)),ROWS(A\$12:A12))-MIN(ROW(H\$2:H\$7))+1),"")

Copy down until you get blanks.

3. Re: Return value from 1st Column based on Data found in corresponding Columns

Wow! Thanks Tony This worked without a hitch

4. Re: Return value from 1st Column based on Data found in corresponding Columns

You're welcome. Thanks for the feedback!

Users Browsing this Thread

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

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