MATCH IF and AND function

Crpat17

New Member
Joined
Sep 24, 2018
Messages
3
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.

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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"
debitCreditunit Noidentifiedunidentifiedcontra
010001
02000
040005
40000

<tbody>
</tbody>
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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?
 
Upvote 0
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
ABCDEF
1debitCreditunit Noidentifiedunidentifiedcontra
20100011000
3020002000
40400054000
5400000
Sheet286
Cell Formulas
RangeFormula
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.
 
Upvote 0
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.

ABCDEF
debitcreditunit noidentifiedunidentified contrRemarks
0100011000 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"
02000BLANK 2000 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"
04000 the credit value has a corresponding contra entry in the debit colum which should be populated into column f -"contra"
40000

<colgroup><col span="6"><col></colgroup><tbody>
</tbody>
hope this clarifies.
 
Upvote 0
Does the formula in my Post # 3 do what you describe?
 
Upvote 0
Hi Jtakw, i am unable to get a result from the formula. can you also please explain how the formula works.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top