Drawing data from rows into columns

[Rob-RD3]

I have been attempting to use a SUMPRODUCT to achieve what I need but after many many hours attempting - I definitely need help.
Hopefully I can do a good job describing what I am attempting to do.

Following are the three things that I am attempting to do:
is "Populate associated cells in the Below Report Table with the data in the Raw Data Table".
Below is an image of part of the Report Table.

There are three challenges that I have encountered.
1. The source outputs all data in rows (top row is the name of the data and the next row is the data).
2. There are 17 cells below and to the right of the "matched" value in the first row that have info that needs to be considered as valid to use. 16 of these 17 cells have headers that have duplicate headers. The good this is that only one set of these will have responses / values in the cells.
Below is an image of one match group in the raw results.

REPORT TABLE
REPORT TABLE.png

RAW RESULTS SAMPLE GROUP
RAW RESULTS SAMPLE GROUP.png

Below is a link to a sample excel workbook that contains the above tables.
https://www.dropbox.com/s/xztirc8v1i...%20Sample.xlsx

Following are the three (3) examples of what I need accomplished (using the excel workbook to describe).
1. Using the categories in column C (e.g., Grading, Storm Sewer System, Retaining Walls, etc.) search / find the category in the raw data table (located to the right of the black divider starting in column H. and pull the comments that are in one of the four (4) comment cells. The first is to pull the "comments" and populate with the comments. The Grading Comments would be pulled from O2 and populated in D2. But one of 4 possible comment cells for Grading will contain text.
The "grading" example is shaded in blue and the results desired are shown in the Report table. This is a text value - challenge to use SUM or SUMPRODUCT.

2. Second, search and match category and populate the associated Costs. The challenge here using either SUM or SUMPRODUCT is that the category associated cost headers (4) all are titled "Total Estimated Cost" and so are all the many other categories cost headers.

3. Third search and match category and populate the associated "Cost Urgency" value for the cost. This is a text value - challenge to use SUM or SUMPRODUCT.

This might help.
- The categories in the Raw Data each cover 17 columns (see Grading shaded in blue). This pattern repeats therefore OFFSET function could be used.

Following are some approaches I have unsuccessfully attempted:
1. SUMPRODUCT. I was able to get the cost to work but could not extend it over many Categories. Text cells would not work.
2. SUM (Array). not successful
3. OFFSET. Knowing the pattern I attempted to use the offset function unsuccessfully.
4. VLOOKUP. I converted the raw data rows into two columns by Major Category (say, EXTERIORS) on a separate worksheet with formulas but the VLOOKUP would not search the formulas. This will be a tool used many times so we do not want the transposing of the rows to columns to be manual.

Your HELP is appreciated

Rob
"Frustrated Excel User"

[JBeaucaire]

Each colored section has a little different formulas in them.

[Rob-RD3]

Thx JBeaucaire - i will look at it and let you know how it works. Yes the bottom end of the Report table was "not real" and typed in by hand to show that INTERIORS existed as well.
Rob

[Rob-RD3]

You definitely FAR better than me! I figured out and understand what you did but it is taking me some time to migrate into the real workbook.
I may have a few followup questions if you do not mind.

Rob