Return value based on nearest date

[drawlings]

Hello

I am using the below formula to return the next action date from a table and this works as expect

=IFNA(INDEX(tbActions[Action Date],MATCH(MIN(IF(tbActions[Pillar]=C7,IF(tbActions[Status]<>"Completed", ABS(tbActions[Action Date]-TODAY())))),IF(tbActions[Pillar]=C7,IF(tbActions[Status]<>"Completed",ABS(tbActions[Action Date]-TODAY()))),0)),"None")

So I am stuck on the following, in the table I am looking up (tbActions) I have an Id column (Id) that I also need to return based on the next date. I am reaching out for suggestions so that the 'Action Item' Column now includes the Id

Thanks in advance

Dan

 

[awoohaw]

what version of excel are you using?
And is it possible to use the xl2bb add in to post your data (Link below)? If you can't do that can you post the data in a table (vs an image)? With images the forum needs to recreate your scenario from scratch. Errors in omission and assumption can lead to more work and worse - bad formulas that work. Please help the forum help you best.

Thanks in Advance.

 

[drawlings]

what version of excel are you using?
And is it possible to use the xl2bb add in to post your data (Link below)? If you can't do that can you post the data in a table (vs an image)? With images the forum needs to recreate your scenario from scratch. Errors in omission and assumption can lead to more work and worse - bad formulas that work. Please help the forum help you best.

Thanks in Advance.

Thanks, I am using Excel 365. See the mini sheets below

CDW - Sample.xlsx
	C	D	E	F	G	H	I
6			Status	Total Actions	Outstanding	Next Date Action	Action Item
7	Hybrid_Platforms		70%	2		06/06/2023
8	Modern_Workspace		0%	0		None
9	Security		0%	1		06/08/2023
10	Digital_Enablement		0%	0		None
Dashboard

Cell Formulas
Range	Formula
D7:D10	D7	=E7
E7	E7	=(tbHybrid[[#Totals],[Status]]/(COUNTA(tbHybrid[Item])*2))
F7:F10	F7	=COUNTIF(tbActions,C7 )
E8	E8	=(tbModern[[#Totals],[Status]]/(COUNTA(tbModern[Item])*2))
E9	E9	=(tbSecurity[[#Totals],[Status]]/(COUNTA(tbSecurity[Item])*2))
E10	E10	=(tbDigital[[#Totals],[Status]]/(COUNTA(tbDigital[Item])*2))
H7:H10	H7	=IFNA(INDEX(tbActions[Action Date],MATCH(MIN(IF(tbActions[Pillar]=C7,IF(tbActions[Status]<>"Completed", ABS(tbActions[Action Date]-TODAY())))),IF(tbActions[Pillar]=C7,IF(tbActions[Status]<>"Completed",ABS(tbActions[Action Date]-TODAY()))),0)),"None")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H7:H10	Cell Value	="None"	text	YES
D7:D10	Other Type	Icon set		NO

CDW - Sample.xlsx
	B	C	D	E	F	G	H	I
6	Pillar	Stack Type	Action	Action Date	Status	Actioned By	Notes	Id
7	Hybrid_Platforms	Platform Security	Call Joe	06/06/2023	In Progress	abc	Must Call joe to discuss security	Hyb-2
8	Security	Data Security	Call Tim	06/08/2023	In Progress	abc	Must call Tim regarding Data Security	Sec-1
9	Hybrid_Platforms	Storage & Compute	test	08/06/2023	Completed	aa		Hyb-2
10	Total	3						3
Actions

Cell Formulas
Range	Formula
C10	C10	=SUBTOTAL(103,[Stack Type])
I7:I9	I7	=CONCATENATE(LEFT([@Pillar],3),"-",COUNTIF([Pillar],[Pillar]))
I10	I10	=SUBTOTAL(103,[Id])

Cells with Data Validation
Cell	Allow	Criteria
B7:B10	List	=ActionPillar
F7:F10	List	Not Started, In Progress, Completed
C7:C9	List	=INDIRECT(B7)

 

[Peter_SSs]

I am using Excel 365

Please add that to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[drawlings]

Please add that to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done. Thanks

 

[Peter_SSs]

Done. Thanks

Great! Thank you.

 

[awoohaw]

Thanks, I am using Excel 365. See the mini sheets below

CDW - Sample.xlsx
	C	D	E	F	G	H	I
6			Status	Total Actions	Outstanding	Next Date Action	Action Item
7	Hybrid_Platforms		70%	2		06/06/2023
8	Modern_Workspace		0%	0		None
9	Security		0%	1		06/08/2023
10	Digital_Enablement		0%	0		None
Dashboard

Cell Formulas
Range	Formula
D7:D10	D7	=E7
E7	E7	=(tbHybrid[[#Totals],[Status]]/(COUNTA(tbHybrid[Item])*2))
F7:F10	F7	=COUNTIF(tbActions,C7 )
E8	E8	=(tbModern[[#Totals],[Status]]/(COUNTA(tbModern[Item])*2))
E9	E9	=(tbSecurity[[#Totals],[Status]]/(COUNTA(tbSecurity[Item])*2))
E10	E10	=(tbDigital[[#Totals],[Status]]/(COUNTA(tbDigital[Item])*2))
H7:H10	H7	=IFNA(INDEX(tbActions[Action Date],MATCH(MIN(IF(tbActions[Pillar]=C7,IF(tbActions[Status]<>"Completed", ABS(tbActions[Action Date]-TODAY())))),IF(tbActions[Pillar]=C7,IF(tbActions[Status]<>"Completed",ABS(tbActions[Action Date]-TODAY()))),0)),"None")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H7:H10	Cell Value	="None"	text	YES
D7:D10	Other Type	Icon set		NO

CDW - Sample.xlsx
	B	C	D	E	F	G	H	I
6	Pillar	Stack Type	Action	Action Date	Status	Actioned By	Notes	Id
7	Hybrid_Platforms	Platform Security	Call Joe	06/06/2023	In Progress	abc	Must Call joe to discuss security	Hyb-2
8	Security	Data Security	Call Tim	06/08/2023	In Progress	abc	Must call Tim regarding Data Security	Sec-1
9	Hybrid_Platforms	Storage & Compute	test	08/06/2023	Completed	aa		Hyb-2
10	Total	3						3
Actions

Cell Formulas
Range	Formula
C10	C10	=SUBTOTAL(103,[Stack Type])
I7:I9	I7	=CONCATENATE(LEFT([@Pillar],3),"-",COUNTIF([Pillar],[Pillar]))
I10	I10	=SUBTOTAL(103,[Id])

Cells with Data Validation
Cell	Allow	Criteria
B7:B10	List	=ActionPillar
F7:F10	List	Not Started, In Progress, Completed
C7:C9	List	=INDIRECT(B7)

you have a few other tables in your calculations as well, tbHybrid, tbModern, tbSecurity, tbDigital. I am assuming the table you did post is tbActions

 

[drawlings]

you have a few other tables in your calculations as well, tbHybrid, tbModern, tbSecurity, tbDigital. I am assuming the table you did post is tbActions

Yes it is only the tbActions table I am interested in as I am trying to figure out the best formula to retrieve the Id column like I've done with the next date.