Best way to handle IF/THEN formula

[rck]

Good morning,
I am using an IF/THEN statement, but I didn't think it all the way through before I started using it.

=IF(AND(A2>=TODAY(),B2=$O$2), $P$2, IF(AND(A2>=TODAY(),B2=$O$3), $P$3, IF(AND(A2>=TODAY(),B2=$O$4), $P$4, "N/A")))

Date	Shift	Total Staff	Minimum Staffing	Open Positions	Scheduled	BC (+1)	Average Unscheduled	Projected Residual	Projected Above/Below Minimum Staffing	Actual Unscheduled	Actual Residual	Actual Above/Below Minimum Staffing		Shift	Open Positions
01/01/2023	A	56	46	N/A	6	0	3	47	1		50	4		A	0
01/02/2023	B	56	46	N/A	6	0	3	47	1		50	4		B	1
01/03/2023	C	56	46	2	6	0	3	45	-1		48	2		C	2

The problem is that the formula is doing exactly what it is being told to do. However, I would also like for the cell to contain historical values. If the value in $O$2 was "0" on 1/1/2023, then keep the one. If the value in $O$2 changes to 3 on 1/4/2023, store "3" for that date, but keep the "0" on 1/1 (don't change 1/1 to "3").

Is there a better way to handle this by only making changes to $O$2:$O$4?

Thank you in advance!

 

[hajiali]

I’m a bit confused. Seems like your comparing column B and O which contain letters. In your explanation you said if Column O is “0”.

 

[Fluff]

I would also like for the cell to contain historical values.

That is not possible. You would need to have an expanding list that included the date & then refer to that.