MATCH IF and AND function

[Crpat17]

Hi, i am new to this forum and request some help on excel.

i intend to use 3 conditions on the below table. the formula i used is =if(isnumber(match(b2,A2:A5,0)*AND(c2="",b2,0) however i am unable to extract the required result.
[TABLE="width: 664"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

to explain my request. the isnumber match cross verifies the credit column with the debit column to identify contra entries
Since the credit column does not have a contra entry and the unit number is available, the value of 1000 needs to be populated in "identified"
the second row has a value under credit column but does not have a value entered under unit which should be populated in "unidentified"
the 3rd column has a contra entry for the same value and also has a unit number entered, however since this is a contra entry the value should be populated under "contra"
[TABLE="width: 500"]
<tbody>[TR]
[TD]debit[/TD]
[TD]Credit[/TD]
[TD]unit No[/TD]
[TD]identified[/TD]
[TD]unidentified[/TD]
[TD]contra[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1000[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]4000[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4000[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

This is wrong

=if(isnumber(match(b2,A2:A5,0)*AND(c2="",b2,0)

Unbalanced parentheses, ie ()

and this is wrong

AND(C2="",b2,0)

What are you trying to achieve in this part?

 

[jtakw]

Hi,

Your description is a bit unclear, and does Not match your attempted formula's logic, is this something close to what you want/mean?

Book1
	A	B	C	D	E	F
1	debit	Credit	unit No	identified	unidentified	contra
2	0	1000	1	1000
3	0	2000			2000
4	0	4000	5			4000
5	4000	0			0
Sheet286

Cell Formulas
Range	Formula
D2	=IF($C2="",IF(D$1="unidentified",$B2,""),IF(COUNTIF($A$2:$A$5,$B2),IF(D$1="contra",$B2,""),IF(D$1="identified",$B2,"")))

D2 formula copied down and across to F5.

 

[Crpat17]

Hi jtakw & Special-K99, the required output and to explain further is:

debit and credit column will be posted by me. if there is a contra entry between debit and credit column then the contra value should be pasted in contra as a single value.
if a value is entered under "unit no" then the corresponding credit value should be populated in colum "identified" if not in "unidentified" subject to the entry not being a contra.

[TABLE="width: 1681"]
<colgroup><col span="6"><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]debit[/TD]
[TD]credit[/TD]
[TD]unit no[/TD]
[TD]identified[/TD]
[TD]unidentified [/TD]
[TD]contr[/TD]
[TD]Remarks[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]since the credit value does not have a contra entry and a value is entered under unit the value should be populated into column d - "identified"[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]2000[/TD]
[TD]BLANK[/TD]
[TD] [/TD]
[TD="align: right"]2000[/TD]
[TD] [/TD]
[TD]the credit value does not have a contra entry however the unit value is balnk, so the credit value needs to be populated in column e - "unidentified"[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]4000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]the credit value has a corresponding contra entry in the debit colum which should be populated into column f -"contra"[/TD]
[/TR]
[TR]
[TD="align: right"]4000[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
hope this clarifies.

 

[jtakw]

Does the formula in my Post # 3 do what you describe?

 

[Crpat17]

Hi Jtakw, i am unable to get a result from the formula. can you also please explain how the formula works.