Matching criteria in different rows

[pxtan2]

Hi everyone, I have 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 (column 3) on what values should be obtained.

Worksheet A

[TABLE="width: 232"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Unit[/TD]
[TD]Activity Number[/TD]
[TD]Value

[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]2222[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]4444[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]5555[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]7777[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet B
[TABLE="width: 464"]
<colgroup><col><col span="2"><col span="3"></colgroup><tbody>[TR]
[TD]

Unit


[/TD]
[TD]Activity Number[/TD]
[TD]Activity[/TD]
[TD]In/Out[/TD]
[TD]Position[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]1111[/TD]
[TD]Close[/TD]
[TD]In[/TD]
[TD]Tom[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]1111[/TD]
[TD]Open[/TD]
[TD]In[/TD]
[TD]Tom[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]2222[/TD]
[TD]Close[/TD]
[TD]In[/TD]
[TD]10[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]2222[/TD]
[TD]Open[/TD]
[TD]In[/TD]
[TD]10[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]3333[/TD]
[TD]Close[/TD]
[TD]In[/TD]
[TD]Jane[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]3333[/TD]
[TD]Open[/TD]
[TD]In[/TD]
[TD]Jane[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]4444[/TD]
[TD]Close[/TD]
[TD]Out[/TD]
[TD]20[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]4444[/TD]
[TD]Open[/TD]
[TD]Out[/TD]
[TD]20[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]5555[/TD]
[TD]Close[/TD]
[TD]Out[/TD]
[TD]30[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]5555[/TD]
[TD]Open[/TD]
[TD]Out[/TD]
[TD]30[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]6666[/TD]
[TD]Close[/TD]
[TD]In[/TD]
[TD]Harry[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]6666[/TD]
[TD]Open[/TD]
[TD]In[/TD]
[TD]Harry[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]7777[/TD]
[TD]Open[/TD]
[TD]Out[/TD]
[TD]40[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]7777[/TD]
[TD]Open[/TD]
[TD]Out[/TD]
[TD]40[/TD]
[TD]700[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[Nishant94]

Assuming you have data like this:

Unknown
	A	B	C	D	E	F
1	Unit	Activity Number	Activity	In/Out	Position	Value
2
3	101	1111	Close	In	Tom	100
4	101	1111	Open	In	Tom	100
5	101	2222	Close	In	10	200
6	101	2222	Open	In	10	200
7	101	3333	Close	In	Jane	300
8	101	3333	Open	In	Jane	300
9	101	4444	Close	Out	20	400
10	101	4444	Open	Out	20	400
11	102	5555	Close	Out	30	500
12	102	5555	Open	Out	30	500
13	103	6666	Close	In	Harry	600
14	103	6666	Open	In	Harry	600
15	103	7777	Open	Out	40	700
16	103	7777	Open	Out	40	700
17
Sheet B

[TABLE="width: 85%"]
<tbody></tbody>[/TABLE]

Unknown
	A	B	C
1	Unit	Activity Number	Value
2	101	2222	0
3	101	4444	300
4	102	5555	0
5	103	7777	0
6
Sheet A

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(--('Sheet B'!$B$4:$B$17=B2),--('Sheet B'!$C$3:$C$16="Open"),--('Sheet B'!$C$4:$C$17="Close"),--NOT(ISNUMBER('Sheet B'!$E$3:$E$16)),--('Sheet B'!$A$3:$A$16=A2),--('Sheet B'!$D$4:$D$17="Out"),'Sheet B'!$F$3:$F$16)

Copy the formula down.

 

[Nishant94]

Another Alternative:

Unknown
	A	B	C	D	E	F
1	Unit	Activity Number	Activity	In/Out	Position	Value
2
3	101	1111	Close	In	Tom	100
4	101	1111	Open	In	Tom	100
5	101	2222	Close	In	10	200
6	101	2222	Open	In	10	200
7	101	3333	Close	In	Jane	300
8	101	3333	Open	In	Jane	300
9	101	4444	Close	Out	20	400
10	101	4444	Open	Out	20	400
11	102	5555	Close	Out	30	500
12	102	5555	Open	Out	30	500
13	103	6666	Close	In	Harry	600
14	103	6666	Open	In	Harry	600
15	103	7777	Open	Out	40	700
16	103	7777	Open	Out	40	700
17
Sheet B

Unknown
	A	B	C
1	Unit	Activity Number	Value
2	101	2222	#N/A
3	101	4444	300
4	102	5555	#N/A
5	103	7777	#N/A
6
Sheet A

Cell Formulas
Range	Formula
C2	=INDEX('Sheet B'!$F$3:$F$16,MATCH(1,('Sheet B'!$B$4:$B$17=B2)*('Sheet B'!$C$3:$C$16="Open")*('Sheet B'!$C$4:$C$17="Close")*(NOT(ISNUMBER('Sheet B'!$E$3:$E$16)))*('Sheet B'!$A$3:$A$16=A2)*('Sheet B'!$D$4:$D$17="Out"),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy the formula down.

 

[pxtan2]

Hi Nishant94, thanks so much for your help! Worked beautifully.