If the date is matched in a group of cells then insert the contents of the cell next to date matched

[davids4500]

I have a date in cell B4. If the date in the cells in (OrderShipmentDates!K32:K44) match this date then insert the information in the cell to the left of cell that matches (OrderShipmentDates!J32:J44).
The dates do not follow any chronological order.
Much appreciated
David

 

[arthurbr]

Hi? There is no need shouting at us ...
Which information should be added and where does it come from?

 

[davids4500]

sorry no shouting intended.... just enthusiastic

the date is in B4 on sheet called Budget...
thank you

MonthlyOverviewFiguresforC&S.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
29
30		Scenario 1
31		Order date	Mentor Cut off date Based on SC	Arrival Date + 7 weeks	Order Value $USD	Order Value $AUD	First Payment $USD	First payment $AUD	2nd payment $USD	2nd payment $AUD	2nd payment Date	Freight payment Due	Freight Amount USD	Fumigation Plus $USD	Client orders Value
32										$11,075.83	19-01-2025
33										$0.00	19-02-2025	0-Jan-00	$0.00	-$570.00
34										$0.00	22-03-2025	0-Jan-00	$0.00	-$570.00
35										$9,933.18	25-04-2025	0-Jan-00	$0.00	-$570.00
36										$0.00
37										$17,360.48	15-07-2025	0-Jan-00	$0.00	-$570.00
38										$0.00
39										$8,050.00	03-09-2025	0-Jan-00	$0.00	-$570.00
40										$0.00
41										$9,660.00	04-11-2025	0-Jan-00	$0.00
42										$0.00
43										$0.00	20-12-2025
44	Production Run									$12,544.17	28-06-2025
OrderShipmentDates

Cell Formulas
Range	Formula
L33:L34	L33	=D32
M33:M34	M33	=E32*0.07
L35,L41,L39,L37	L35	=D35
M35,M41,M39,M37	M35	=E35*0.07

 

[davids4500]

Just to add that the date in B4 is =DATE(YEAR(TODAY()), MONTH(TODAY()), 1). SO each month it changes to the current month.

 

[Joe4]

OK, I am not quite clear what you want to put where.

So you are comparing the cells K32:K44 on the "OrderShipmentDate" sheet to the value in cell B4 on your "Budget" sheet.
And if it matches, you want to take the value from column J of that matching row in your "OrderShipmentDate" sheet, and put it where exactly?

 

[davids4500]

OK, I am not quite clear what you want to put where.

So you are comparing the cells K32:K44 on the "OrderShipmentDate" sheet to the value in cell B4 on your "Budget" sheet.
And if it matches, you want to take the value from column J of that matching row in your "OrderShipmentDate" sheet, and put it where exactly?

I would like to put it in D13 on the "Budget" sheet.

MonthlyOverviewFiguresforC&S.xlsx
	B	C	D	E
1	CASH FLOW PROJECTION
2	Date OF Update	Saturday, 11 January 2025
3	Current Month	January-2025
4	Jan-2025	January-2025
5	Income		Amount
6	Total Sales for 12 months
7	Total of Outstanding debtors
8	Cash in everyday account
9	Cash in Savings Account
10	Debtors Due/Available (50%)	35%
11	Current Sales (25%)	25%
12	Payment required this month		$0.00
13	2nd Payment required this month
14	Production Run		$0.00
15	Total In Savings Account For:- Jan 2025		$0.00
BUdget

Cell Formulas
Range	Formula
C2	C2	=TODAY()
C3,B4	C3	=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
C4	C4	=B4
D12	D12	=IF(OR(MONTH(B4)=12,AND(MONTH(OrderShipmentDates!B32)=12,MONTH(B4)=12)),OrderShipmentDates!H32,IF(OR(MONTH(B4)=1,AND(MONTH(OrderShipmentDates!B33)=1,MONTH(B4)=1)),OrderShipmentDates!H33,IF(OR(MONTH(B4)=2,AND(MONTH(OrderShipmentDates!B34)=2,MONTH(B4)=2)),OrderShipmentDates!H34,IF(OR(MONTH(B4)=3,AND(MONTH(OrderShipmentDates!B35)=3,MONTH(B4)=3)),OrderShipmentDates!H35,IF(OR(MONTH(B4)=4,AND(MONTH(OrderShipmentDates!B36)=4,MONTH(B4)=4)),OrderShipmentDates!H36,IF(OR(MONTH(B4)=5,AND(MONTH(OrderShipmentDates!B37)=5,MONTH(B4)=5)),OrderShipmentDates!H37,IF(OR(MONTH(B4)=6,AND(MONTH(OrderShipmentDates!B38)=6,MONTH(B4)=6)),OrderShipmentDates!H38,IF(OR(MONTH(B4)=7,AND(MONTH(OrderShipmentDates!B39)=7,MONTH(B4)=7)),OrderShipmentDates!H39,IF(OR(MONTH(B4)=8,AND(MONTH(OrderShipmentDates!B40)=8,MONTH(B4)=8)),OrderShipmentDates!H40,IF(OR(MONTH(B4)=9,AND(MONTH(OrderShipmentDates!B41)=9,MONTH(B4)=9)),OrderShipmentDates!H41, IF(OR(MONTH(B4)=10,AND(MONTH(OrderShipmentDates!B42)=10,MONTH(B4)=10)),OrderShipmentDates!H42, IF(OR(MONTH(B4)=11,AND(MONTH(OrderShipmentDates!B43)=11,MONTH(B4)=11)),OrderShipmentDates!H43))))))))))))
B15	B15	=CalculationsInfo!J22&" "&TEXT(EDATE(B4, 0),"mmm yyyy")
D14	D14	=OrderShipmentDates!H44
D15	D15	=SUM(D9+D10+D11)-(D12+D13+D14)

 

[Joe4]

Is it possible that multiple cells match the date, so you would be adding up multiple rows?
If so, you will probably want to use a SUMIF formula.

 

[davids4500]

Is it possible that multiple cells match the date, so you would be adding up multiple rows?
If so, you will probably want to use a SUMIF formula.

Dear Joe4. Yes that could be a possibility. Good point

 

[Joe4]

Then I think your formula should look something like this:

=SUMIF(OrderShipmentDates!K32:K44,Budget!B4,OrderShipmentDates!J32:J44)