index/match help

[Paul T]

Hi all

Struggling with a lookup result.

Below is example sheet


A
B
C
D
E

1
ABDX02KI01
New Business
11/03/2006
12


2
ABDX02TW01
Charge
11/03/2006
15


3
ABDX02TW01
New Business
11/03/2006
217.35
result =D2

4
ABDX02UL01
New Business
11/03/2006
30


5
ABJX01HQ01
Adjustment
04/11/2005
73.91


6
ABJX01HQ01
Charge
01/02/2006
-73.91


7
ABMX03TW01
Charge
16/03/2006
15


8
ABMX03TW01
New Business
16/03/2006
1459.92
i.e. D7

9
ABMX03TW01
Charge
16/03/2006
-104.92


10
ABMX03UL01
New Business
16/03/2006
30
#n/a



Basically look up values a3 & C3 see if there is a match for b2 for a2 & c2 and display d2 result in e3.
and find simular examples in the rest of the sheet i.e 7 & 8 match but 9 & 10 dont (as different ref).

Appreciate any pointers.
Thanks
Paul

 

[Ardus Petus]

Insert a blank row before Row 1,
enter formula in E2, and copy down:
=IF(AND(A2=A1,C2=C1),D1,"")

HTH
--
AP

"Paul T" <[email protected]> a écrit dans le message de Hi all

Struggling with a lookup result.

Below is example sheet


A
B
C
D
E

1
ABDX02KI01
New Business
11/03/2006
12


2
ABDX02TW01
Charge
11/03/2006
15


3
ABDX02TW01
New Business
11/03/2006
217.35
result =D2

4
ABDX02UL01
New Business
11/03/2006
30


5
ABJX01HQ01
Adjustment
04/11/2005
73.91


6
ABJX01HQ01
Charge
01/02/2006
-73.91


7
ABMX03TW01
Charge
16/03/2006
15


8
ABMX03TW01
New Business
16/03/2006
1459.92
i.e. D7

9
ABMX03TW01
Charge
16/03/2006
-104.92


10
ABMX03UL01
New Business
16/03/2006
30
#n/a



Basically look up values a3 & C3 see if there is a match for b2 for a2 & c2 and display d2 result in e3.
and find simular examples in the rest of the sheet i.e 7 & 8 match but 9 & 10 dont (as different ref).

Appreciate any pointers.
Thanks
Paul