I thought I may have to do that, the explanation is a little long, so here goes:
2-Sheets I am working from:
Sheet 3 is the Project Data
Sheet 4 is the Program/Store Data

Using these examples 105 as the store #, Ceiling Tile as the Project, Complete for the Project Status and 2012-11-05 as the date
In Sheet 4 Column H is where all the data will be combined I have this to set it up as such:
"Dim rng7 As Range 'Report Compiled Projects" and "Set rng7 = Sheet4.Range("H2:H" & Range("A" & Rows.Count).End(xlUp).Row)"


Here is where the fun starts; a store (represented by column A) can have 0-26 different projects (which is an entirely different issue). The projects start in column Z.
So, ***"=IF(Z3=""N/A"","""",IF(Z3=""OOS"",Z$1&""-""&""OOS""*** tells me if the project in Sheet 4 Column Z is either: N/A- no project or OOS-Out Of Scope

If it is then determined that the project is neither OOS or N/A then I need to find the status of that Project. This information is stored on Sheet 3
Now I need the Project Status: INDEX(Sheet3!$N$2:$N$10000
Match that with the Sto MATCH(1,($A3=Sheet3!$C$2:$C$10000)
Match that with the Project Name: *(Sheet4!Z$1=Sheet3!$B$2:$B$10000),0)))
AND if the match to the Project Name is an error then:
Z3,INDEX(Sheet3!$N$2:$N$10000,MATCH(1,($A3=Sheet3! $C$2:$C$10000)*(Sheet4!Z$1=Sheet3!$B$2:$B$10000),0 )))))
Therefore the possible results in Column H would be:
Blank for the 1st If
Ceiling Tile-OOS for the 2nd If
Ceiling Tile-Complete for the 3rd If
Ceiling Tile-2012-11-05 for the 4th If

Then I need to do this anywhere from 1-26 times over columns AA, AD, BA etc...
Does this help in explaining it?

Thanks