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

##### New Member
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>

Last edited:

#### c_m_s_jr

##### Well-known Member
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.

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

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.

##### New Member
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!!!

##### New Member
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!!!

Last edited:

#### c_m_s_jr

##### Well-known Member
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.

##### New Member
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.

1,082,360
Messages
5,364,939
Members
400,815
Latest member
Joaquin Phoenix

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
• Complex Heat Map using conditional formatting
Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
• Conditional formatting
Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...