Excel 2007 : looking up/indexing a value based on multiple criteria (=, <=)

Thanks in advance,

I have attached a data sample with columns showing what I have tried to do without success.

Basically I have 2 separate tables with separate sets of data that I want to relate.

In the first table (A1:C32) I have (Date, Location, Height) and in the second table (E1:G32) I have (Heading, Date. D., Depth).

What I want to do is in the depth column in table 2 search for a height value from table one that satisfies the following criteria:
[Location] = [Heading] and [Date] is the smallest date value greater than or equal to [Date. D.].

I have tried both using the lookup function, with the following formula {=LOOKUP(2, 1/($B$2:$B$32&$A$2:$A$32=E2&F2), $C$2:$C$32)}, which works well but only returns values for exact matches, as well as an Index and Match function {=INDEX($C$2:$C$32, MATCH(IF(E2=$B$2:$B$32, F2), $A$2:$A$32, -1)*(E2=$B$2:$B$32))} which seems to work until the heading value changes from the first value (of A10) in both tables. If anyone could let me know what I am missing or if there is a simpler solution please advise.

Thank you.

Edit: Forgot array parenthesis around formulas

Hello cadojib, Welcome to the forum!

Since forum been allowed to attach files please attach your file on the file instead of a link. Most of the members are not following the link. Go to

Go Advanced >> Manage Attachments, and browse your file.

Try this Array Formula,

=INDEX(C$2:C$32,MATCH(1,IF(B$2:B$32=E2,IF(A$2:A$32<=F2,1)),0))