Matching criteria in different rows

Hi everyone, I have attached a workbook containing two worksheets (A & B). I would like a formula/macros to lookup B, find the value in row n, and return it in A. I have provided examples in A on what values should be obtained.

As shown, there are specific criteria in Worksheet B that must be fulfilled before the value can be identified:

1. Matching "Activity Number" must be in row n+1.
2. "Activity" in row n must be "Open", while "Close" in row n+1.
3. "Position" in row n must be of non-numeric value.
4. "Unit" in row n must match that in Worksheet A.
5. "In/Out" in row n+1 must be "Out"

I would welcome any help! Thanks so much in advance!

Hi Dave,

Sorry, I meant having non-numeric values for "Position" in row n+1.

I was urgently requiring a solution to this, hence the cross post in different forums- will take note next time, sorry about that!

Anyway, a solution was kindly provided (which worked) in MrExcel: http://www.mrexcel.com/forum/excel-q...rent-rows.html