Excel advanced lookup/reference formula required

I am trying to lookup a value in one sheet based on three criteria. The lookup formula resides in the Operational Data sheet which contains data from the machine which reports every ten minutes and has a structure similar to the table below (simplified for the sake of simplification)

DateTime                  Status   =>   ShouldBe
01/12/2010  01:00:01      ?             16
01/12/2010  02:00:01      ?             16
01/12/2010  03:00:01      ?             16
01/12/2010  04:00:01      ?             0
01/12/2010  05:00:01      ?             0
01/12/2010  06:00:01      ?             0
...
08/12/2010  07:00:01      ?             204

The Status column should reference the Status Data sheet which contains data of the machine's operating status. This only reports change in the machine's status, ie is not periodic. The Status Data sheet has a structure similar to that below (again simplified for the sake of simplification)

Error Code        Start Time                       End Time
16                01/12/2010  00:00:01             01/12/2010  03:08:56
0                 01/12/2010  03:08:56             06/12/2010  12:01:21
204               06/12/2010  12:01:21         开发者_运维技巧    15/12/2010  01:14:01
0                 15/12/2010  01:14:01             20/12/2010  09:12:42

Any ideas?

-- edit

From a quick web search there appers to be two approaches to lookup with mulitple criteria. This first being to add another column of concatenated valeus and using VLOOKUP on a concatenated string. This is not appropriate because I am trying to search based on < and > (less/greater then) on the date columns.

I couldn't figure out how to use MATCH for this because the third argument defines weather the LOOKUP is a a greater/less than and I can't combine the two.

For example:

'Operation Data'!DateTime > 'Status Data'!StartTime and 'Operation Data'!DateTime < 'Status Data'!EndTime

If on sheet 'Status' Error codes are in Column A and Start time in Column B

on sheet 'Operational Data' DateTime is in column A

Formula is, for a cell in row 2

INDEX('Status'!$A:$A,MATCH(A2,'Status'!$B:$B,1))

Assumes data in sheet 'Status' is sorted ascending on Start Time

I think you have a couple of options:

1) You add a new column which is a concatenation of your 3 lookup criteria and do your lookup based on this.

2) The second option is an INDEX MATCH array formula, there is an example on here: http://www.ozgrid.com/forum/showthread.php?t=46692&page=1