Need help with Excel formula to find a group of data and return a value in that data set.

[JadeWitte]

I have a fairly large spreadsheet with about 5,000 lines and 100+ columns. I've taken a small piece of it and posted below to give you the layout of the spreadsheet. Row 2 is a number assigned to each month over the next 10 years.

What I'm trying to do is write a formula that will look specifically at a column (in this example Column E). I want the formula to scroll down to the first instance of a headcount for all particular grouping (which in the example below, the grouping are separated by the headcount rows). If it is the first time headcount has been introduced for the grouping then the formula will determine which group and the "PGM" amount. In the example below, for column E, The grouping in which there is new headcount added is in row 22 (specifically the trigger is the value in E22, since there is 0 or "" in cell D22). Once I determine the grouping then I need the PGM value which in this example is cell: E28.

I tried doing an HLOOKUP nested within a VLOOKUP, but the problem was it created a circular reference (since certain cells were in each other's arrays). I created a massive IF statement that DID work, but it is extremely cumbersome, and probably not the way to do it. My IF statement basically went down Columns D & E and looked at Rows 4, 13,22, 31...etc. etc..all the way down...until I had this massive formula that nearly maxed out the number of characters for a formula (8,150 characters I think). In my IF statement if Column D was blank AND Column E was greater than 1, then I would request value in E28 in this example below. Is there way to do this without a massive, clumsy nested IF statement? Thank you for your assistance!!!

A	B	C	D	E	F	G	H	I
1		Mar	Apr	May	Jun	Jul	Aug	Sep
2		32	33	34	35	36	37	38
3
4	Headcount	2	2	2	3	3	3	4
5	Revenue	16,200	16,200	16,200	24,300	24,300	24,300	32,400
6	Direct Cost	12,085	12,085	12,085	18,128	18,128	18,128	24,170
7	DPM	4,115	4,115	4,115	6,172	6,172	6,172	8,230
8	%DPM	25.40%	25.40%	25.40%	25.40%	25.40%	25.40%	25.40%
9	Indirect Cost	486	486	486	729	729	729	972
10	PGM	3,629	3,629	3,629	5,443	5,443	5,443	7,258
11	%PGM	22.40%	22.40%	22.40%	22.40%	22.40%	22.40%	22.40%
12
13	Headcount		2	2	3	3	3	4
14	Revenue	0	15,400	15,400	23,100	23,100	23,100	30,800
15	Direct Cost	0	11,488	11,488	17,233	17,233	17,233	22,977
16	DPM	0	3,912	3,912	5,867	5,867	5,867	7,823
17	%DPM		25.40%	25.40%	25.40%	25.40%	25.40%	25.40%
18	Indirect Cost	0	462	462	693	693	693	924
19	PGM	0	3,450	3,450	5,174	5,174	5,174	6,899
20	%PGM		22.40%	22.40%	22.40%	22.40%	22.40%	22.40%
21
22	Headcount			2	2	3	3	3
23	Revenue	0	0	15,400	15,400	23,100	23,100	23,100
24	Direct Cost	0	0	11,488	11,488	17,233	17,233	17,233
25	DPM	0	0	3,912	3,912	5,867	5,867	5,867
26	%DPM			25.40%	25.40%	25.40%	25.40%	25.40%
27	Indirect Cost	0	0	462	462	693	693	693
28	PGM	0	0	3,450	3,450	5,174	5,174	5,174
29	%PGM			22.40%	22.40%	22.40%	22.40%	22.40%
30
31	Headcount				2	2	2	3
32	Revenue	0	0	0	16,200	16,200	16,200	24,300
33	Direct Cost	0	0	0	12,085	12,085	12,085	18,128
34	DPM	0	0	0	4,115	4,115	4,115	6,172
35	%DPM				25.40%	25.40%	25.40%	25.40%
36	Indirect Cost	0	0	0	486	486	486	729
37	PGM	0	0	0	3,629	3,629	3,629	5,443
38	%PGM				22.40%	22.40%	22.40%	22.40%

<tbody>
</tbody>

 

[c_m_s_jr]

I have a fairly large spreadsheet with about 5,000 lines and 100+ columns. I've taken a small piece of it and posted below to give you the layout of the spreadsheet. Row 2 is a number assigned to each month over the next 10 years.

What I'm trying to do is write a formula that will look specifically at a column (in this example Column E). I want the formula to scroll down to the first instance of a headcount for all particular grouping (which in the example below, the grouping are separated by the headcount rows). If it is the first time headcount has been introduced for the grouping then the formula will determine which group and the "PGM" amount. In the example below, for column E, The grouping in which there is new headcount added is in row 22 (specifically the trigger is the value in E22, since there is 0 or "" in cell D22). Once I determine the grouping then I need the PGM value which in this example is cell: E28.

I tried doing an HLOOKUP nested within a VLOOKUP, but the problem was it created a circular reference (since certain cells were in each other's arrays). I created a massive IF statement that DID work, but it is extremely cumbersome, and probably not the way to do it. My IF statement basically went down Columns D & E and looked at Rows 4, 13,22, 31...etc. etc..all the way down...until I had this massive formula that nearly maxed out the number of characters for a formula (8,150 characters I think). In my IF statement if Column D was blank AND Column E was greater than 1, then I would request value in E28 in this example below. Is there way to do this without a massive, clumsy nested IF statement? Thank you for your assistance!!!

A	B	C	D	E	F	G	H	I
1		Mar	Apr	May	Jun	Jul	Aug	Sep
2		32	33	34	35	36	37	38
3
4	Headcount	2	2	2	3	3	3	4
5	Revenue	16,200	16,200	16,200	24,300	24,300	24,300	32,400
6	Direct Cost	12,085	12,085	12,085	18,128	18,128	18,128	24,170
7	DPM	4,115	4,115	4,115	6,172	6,172	6,172	8,230
8	%DPM	25.40%	25.40%	25.40%	25.40%	25.40%	25.40%	25.40%
9	Indirect Cost	486	486	486	729	729	729	972
10	PGM	3,629	3,629	3,629	5,443	5,443	5,443	7,258
11	%PGM	22.40%	22.40%	22.40%	22.40%	22.40%	22.40%	22.40%
12
13	Headcount		2	2	3	3	3	4
14	Revenue		15,400	15,400	23,100	23,100	23,100	30,800
15	Direct Cost		11,488	11,488	17,233	17,233	17,233	22,977
16	DPM		3,912	3,912	5,867	5,867	5,867	7,823
17	%DPM		25.40%	25.40%	25.40%	25.40%	25.40%	25.40%
18	Indirect Cost		462	462	693	693	693	924
19	PGM		3,450	3,450	5,174	5,174	5,174	6,899
20	%PGM		22.40%	22.40%	22.40%	22.40%	22.40%	22.40%
21
22	Headcount			2	2	3	3	3
23	Revenue			15,400	15,400	23,100	23,100	23,100
24	Direct Cost			11,488	11,488	17,233	17,233	17,233
25	DPM			3,912	3,912	5,867	5,867	5,867
26	%DPM			25.40%	25.40%	25.40%	25.40%	25.40%
27	Indirect Cost			462	462	693	693	693
28	PGM			3,450	3,450	5,174	5,174	5,174
29	%PGM			22.40%	22.40%	22.40%	22.40%	22.40%
30
31	Headcount				2	2	2	3
32	Revenue				16,200	16,200	16,200	24,300
33	Direct Cost				12,085	12,085	12,085	18,128
34	DPM				4,115	4,115	4,115	6,172
35	%DPM				25.40%	25.40%	25.40%	25.40%
36	Indirect Cost				486	486	486	729
37	PGM				3,629	3,629	3,629	5,443
38	%PGM				22.40%	22.40%	22.40%	22.40%

<TBODY>
</TBODY>

So this will address the specific scenario you described, but something tells me it doesn't meet what ALL your needs really are. Perhaps you'll be able to modify it to meet what you really need.

=INDEX($E$1:$E$38,SUMPRODUCT(--($B$1:$B$38="HEADCOUNT")*--($C$1:$C$38="")*--($D$1:$D$38="")*--($E$1:$E$38>1)*A1:A38)+6,1)

Not sure where you need to have this formula place, but for column E:


1) It will look for which row has the heading headcount
2) identify where column C, and Column D = ""
3) identify where column E > 1

Once it has verified which row has headcount and a "" in column C and column D and column E is >1 it will return the row number from column A and add 6 since that is how many rows down PGM is from headcount.

The row number from above is passed into the INDEX function which uses that value to know which row number to pick returning the PGM value from COLUMN E.

 

[JadeWitte]

Thank you CMS, jr.!!! I've never used the SUMPRODUCT function, but will from now on! Really cool. So absolutely the formula will work the way you wrote it for the example I gave you, however, my example (the way I illustrated it) was not completely accurate. Column A is not a series of numbers 1 to 38, but rather me trying to number the rows for the sake of trying to depict Excel to help solve the problem. In other words Column A is where the data starts (or the column for "Headcount"). Having said that, I could insert a column at the beginning and easily add 1,2,3....all the way down and use your formula as is (modifying the column names, etc). But I would prefer not insert this number column at the front if not necessary...if there is a different way. I'm assuming the formula needs these numbers to know what number to add "6" to in order to get to the "PGM" number, so maybe I have to insert this column. I can always then "hide" it if it looks strange. Curious if there is a different way? Also, what does the "1" indicate at the very end of the formula? Thanks again!! I've spent countless hours trying to figure this out and you have really taught me something new that I'm excited to start leveraging!!!

Regards, Jade

 

[JadeWitte]

Thanks again. This formula works fairly well for me after doing some modifications as you indicated I might have to do. The challenge that I'm having is that I really need the $E$1:$E$38 array to be dynamic versus static. Here's why. I'm feeding numbers into this spreadsheet from another one. Based upon a VLOOKUP on that other spreadsheet I'm either adding +2, +1, 0, or -2 to the month identifiers on the spreadsheet above. Specifically in this example (34) under May. So let's say, my VLOOKUP on the other spreadsheet returns a (+1). I would add +1 to 34 and the month (column) I would want to use for the INDEX (SUMPRODUCT) formula above is 35 (or the Month of June). If the VLOOK came back with -2, I would subtract 2 from 34 and want the column 32 (or March). I would want to get the PGM number for the data group just like above but I want the column to be dynamic rather than hard-coded as is the case now. Can you or anyone else help me do this, please? I've been researching MATCH, INDEX, V & HLOOKUP functions all day and know that I'm close to getting it. Thank you!!!

 

[c_m_s_jr]

Sorry I didn't respond, had to focus on work today. Take a look at using the OFFSET function. It always you to move ranges or position. So you could utilize the 1, -1, -2 returned from the VLOOKUP withing the OFFSET to know which column , E, F, Etc to be in.

 

[JadeWitte]

No worries...I've been going down the OFFSET path this afternoon but not sure how to construct the formula exactly. I should hopefully figure it out tonight. But, I'm with you, I think OFFSET is the way to go potentially. Thanks for responding.