Sumproduct or Match & Index?

Don't know what's happened, but all my Excel knowledge has melted out of my ears.

I have a sheet (sheet 3) that is a calendar of sorts and looks like this:

Please Login or Register  to view this content.

On another sheet (sheet 1) I have a dump of information from a database that is a jumbled mess of data. Down column A are listed the office names, and columns B through to U contain information about each office. The problem is that the way the database has been setup to dump information into the CSV file, I end up with something that looks a little like this:

Please Login or Register  to view this content.

Which isn't helpful.

The Letters at the end are days of the week on which the office is closed. They only appear on the first row of each office data set.

What I need to do is enter a formula on my calendar sheet (sheet 3) that looks up the office name in column A, checks it against the range of data on sheet 1 and enters "Closed" in the cell if any of the days are listed. So for the data above, it would look a little like this:

Please Login or Register  to view this content.

I know I'm probably being incredibly dense and it's a sumproduct deal as my previous query was, but I can't make it work this time around.

Any ideas?

jennyaccord,

Attached is an example workbook based on the criteria described. In Sheet3 cell B2 is this formula:
=IF(COUNTIF(INDIRECT("Sheet1!"&MATCH($A3,Sheet1!$A:$A,0)&":"&MATCH($A3,Sheet1!$A:$A,0)),B$1)>0,"Closed","")

And then its copied over and down. Is something like that what you're looking for?

Try this in Sheet3!B3, copy down & across.

=IF(ISNUMBER(MATCH(B$1,INDEX(Sheet1!$B:$U,MATCH($A3,Sheet1!$A:$A,0),0),0)),"Closed","")

If you can attach a dummy file would helpful to see your layout.