Return a value from a reference based on multiple Columns

Question

Could someone please guide me&nbsp;I am trying to formulate the data below on a seperate sheet, by looking up the Channel, by Due date and return the value (outstanding Due)ChannelCustomerSales CategoryInvoice NumbersInvoiced DatesDue Date&nbsp;Outstanding (Due)Distribution Fees - Convenience TotalTotal Pellissier&nbsp;IAC Beverage SalesIAC000369211-Aug30-Sep&nbsp;PdDistribution Fees - Convenience EngenEngen Garage - Spartan&nbsp;IAC Beverage SalesIAC000369615-Aug30-Sep&nbsp;PdDistribution Fees - Convenience TotalTotal Petroport N3 East&nbsp;IAC Beverage SalesIAC000016-Mar30-Mar&nbsp;PdDistribution Fees - Convenience TotalTotal Petroport N3 West&nbsp;IAC Beverage SalesIAC000016-Mar30-Mar&nbsp;PdDistribution Fees - Convenience BP ExpressBP Trompsburg&nbsp;IAC Beverage SalesIAC000000322-Aug30-Sep&nbsp;PdDistribution Fees - Convenience TotalTotal Petroport N3 East&nbsp;IAC Beverage SalesIAC000000428-Aug30-Sep&nbsp;PdDistribution Fees - Convenience TotalTotal Petroport N3 West&nbsp;IAC Beverage SalesIAC000000528-Aug30-Sep&nbsp;Pd

martin_angosto · Answer

Jeffvd671860&nbsp;&nbsp;See attached workbook with proposed solution.&nbsp;=IFERROR(INDEX(Sheet1!A2:G8,MATCH(1,(Sheet2!B2=Sheet1!A2:A8)*(Sheet2!B3=Sheet1!F2:F8),0),7),"Not found")

jeffvd67 · Answer

Martin_Angosto&nbsp;Thank you, however im trying to pull the total values from Column Outstanding Payments, these might be paid up of have multiple values by month. I will try your suggestion&nbsp;

jeffvd67 · Answer

Martin_Angosto&nbsp;I have refreshed the spreadsheet and tried to breakdown the following:&nbsp;&nbsp;I am trying to pull the values by month for each Customer. &nbsp;This will be on a seperate sheet hence Sheet 1 and sheet 2&nbsp;ABCDEFGH&nbsp;1SHEET 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2CustomerChannelSales CategoryInvoice NumbersInvoiced DatesDue DateOutstanding (Due)Invoiced&nbsp;3BP, FourwaysDistribution Fees - Convenience TotalIAC Beverage SalesIAC000369211-Aug30-Jul3,598.493,598.49&nbsp;4Engen Garage - SpartanDistribution Fees - Convenience EngenIAC Beverage SalesIAC000369615-Aug30-May1,347.311,347.31&nbsp;5Total Petroport N3 EastDistribution Fees - Convenience TotalIAC Beverage SalesIAC000016-Mar30-JunPd1,195.54&nbsp;6Total Petroport N3 WestDistribution Fees - Convenience TotalIAC Beverage SalesIAC000016-Mar30-Apr2,391.082,391.08&nbsp;7BP TrompsburgDistribution Fees - Convenience BP ExpressIAC Beverage SalesIAC000000322-Aug30-Sep2,918.492,918.49&nbsp;8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9Sheet 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10Income&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11Distribution Fees - Convenience BP ExpressCustomer Sales Income31-Jul&nbsp;#N/A&nbsp;&nbsp;&nbsp;&nbsp;12Distribution Fees - Convenience EngenCustomer Sales Income31-Jul&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;13Distribution Fees - Convenience PnP Direct StoresCustomer Sales Income31-Jul&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;14Distribution Fees - Convenience SasolCustomer Sales Income31-Jul&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;15Distribution Fees - Convenience TotalCustomer Sales Income31-Jul&nbsp;-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

Return a value from a reference based on multiple Columns

3 Replies

Resources

Channel	Customer	Sales Category	Invoice Numbers	Invoiced Dates	Due Date	Outstanding (Due)
Distribution Fees - Convenience Total	Total Pellissier	IAC Beverage Sales	IAC0003692	11-Aug	30-Sep	Pd
Distribution Fees - Convenience Engen	Engen Garage - Spartan	IAC Beverage Sales	IAC0003696	15-Aug	30-Sep	Pd
Distribution Fees - Convenience Total	Total Petroport N3 East	IAC Beverage Sales	IAC0000	16-Mar	30-Mar	Pd
Distribution Fees - Convenience Total	Total Petroport N3 West	IAC Beverage Sales	IAC0000	16-Mar	30-Mar	Pd
Distribution Fees - Convenience BP Express	BP Trompsburg	IAC Beverage Sales	IAC0000003	22-Aug	30-Sep	Pd
Distribution Fees - Convenience Total	Total Petroport N3 East	IAC Beverage Sales	IAC0000004	28-Aug	30-Sep	Pd
Distribution Fees - Convenience Total	Total Petroport N3 West	IAC Beverage Sales	IAC0000005	28-Aug	30-Sep	Pd

	A	B	C	D	E	F	G	H
1	SHEET 1
2	Customer	Channel	Sales Category	Invoice Numbers	Invoiced Dates	Due Date	Outstanding (Due)	Invoiced
3	BP, Fourways	Distribution Fees - Convenience Total	IAC Beverage Sales	IAC0003692	11-Aug	30-Jul	3,598.49	3,598.49
4	Engen Garage - Spartan	Distribution Fees - Convenience Engen	IAC Beverage Sales	IAC0003696	15-Aug	30-May	1,347.31	1,347.31
5	Total Petroport N3 East	Distribution Fees - Convenience Total	IAC Beverage Sales	IAC0000	16-Mar	30-Jun	Pd	1,195.54
6	Total Petroport N3 West	Distribution Fees - Convenience Total	IAC Beverage Sales	IAC0000	16-Mar	30-Apr	2,391.08	2,391.08
7	BP Trompsburg	Distribution Fees - Convenience BP Express	IAC Beverage Sales	IAC0000003	22-Aug	30-Sep	2,918.49	2,918.49
8
9	Sheet 2
10	Income
11	Distribution Fees - Convenience BP Express	Customer Sales Income	31-Jul		#N/A
12	Distribution Fees - Convenience Engen	Customer Sales Income	31-Jul		-
13	Distribution Fees - Convenience PnP Direct Stores	Customer Sales Income	31-Jul		-
14	Distribution Fees - Convenience Sasol	Customer Sales Income	31-Jul		-
15	Distribution Fees - Convenience Total	Customer Sales Income	31-Jul		-