Return Value of Searched Item In Combination With IF

PostIt

New Member
Joined
Jun 3, 2015
Messages
11
DateCompany NameMethodCashCheckCCWireInvoice#Invoice TotalAmount PaidStill Due
03/08/16MONAcc$50045230$500$500$-
03/08/16CRANBERRYcash$100045231$1000$1000$-

<tbody>
</tbody>






This is the Checks Sheet
Invoice DateInvoice #CustomerDue DateAmount DuePaid DateTotal PaidMethodCredit AmountCMRType of Credit
03/08/1645230MONA$50003/08/16$250cc$250CMR6789
03/08/1645231CRANBERRY$100003/08/16$1000cc

<tbody>
</tbody>
This is the Invoices Sheet

Windows 10
Microsoft Office Home and Student 2010
Excel Version 14.0.7 (32-bit)


I have 3 sheets.
Invoices (Paid and unpaid invoices)
Customers (All customer data)
Checks (Received payment)

Date:
Code:
=IFERROR(INDEX(Invoices,MATCH(I24,Invoices[Invoice '#],0),6),"")

Company Name:
Code:
=IFERROR(INDEX(Invoices,MATCH(I24,Invoices[Invoice '#],0),3),"")

Invoice Total:
Code:
=IFERROR(INDEX(Invoices,MATCH(I24,Invoices[Invoice '#],0),5),"")

Amount Paid:
Code:
=SUMIF(Checks[@[Cash]:[Wire]], ">0",Checks[@[Cash]:[Wire]])

CC:
Code:
=(IF(ISNUMBER(SEARCH("cc",D24)),[@[Invoice Total]],))

Still Due:
Code:
=IF(C32=""," - ",IF(OR($C$3="All Customers",$C$3=C32),[@[Invoice Total]]-[@Cash]-[@Check]-[@CC]-[@Wire]," --- "))

The issue I'm having is that generally, CC charges are paid in full.
The rare occasions that they are partially paid for or there's been a credit issued, I would like for the CC formula to be able to take the cc amount and subtract the credit amount by looking up the Invoice # and searching across that row until it comes to the credit amount.
Which would in turn be =(IF(ISNUMBER(SEARCH("cc",D24)),[@[Invoice Total]],))-(Return Search Value).

Any help would be appreciated.
Thank you!
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,122,213
Messages
5,594,874
Members
413,945
Latest member
V51773

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
Top