Single column result based on 2 columns and 5 ranges

[bomijoff]

Morning!
Firstly sorry for the terrible thread name.
I'm looking to auto populate column C based on the date of B looking at which range it fits between on E&F with the result of G, if B is blank then to look at A and again look at which date fits between E&F with the result being G.

Examples:
C2 would look at the date of B2 to see which range it fits between E & F, since it is 2-Feb-23 the result would be TBC as it sits between the dates of E6 & F6. Doesn't need to look at A2 as B2 is populated.
C4 would look at the date of B4 and see that it's blank so would then look at A4 and find the range it fits between in E & F, since it is 6-Feb-13 the result would be 31-Dec-23 as it sits between the dates of E4 & F4.

I was looking at doing something like B2=">="&E2 and B2="=<"&F2 then result is G2, if B2 is blank then A2=">="&E2 and A2"=<"&F2. i got lost when i realised it would be a bigger formula and trying to actually make it work and fit it together!

Just a note that column A has no blank fields.
Any help would be greatly appreciated!

 

[Anonymous1378]

Consider posting some XL2BB in the future.

Anyway try:

Book89
	A	B	C	D	E	F	G
1
2	03-Jan-08	02-Feb-23	TBC		01-Dec-06	31-Dec-09	31-Mar-22
3	01-May-11	02-Feb-13	31-Dec-23		01-Jan-10	31-Dec-12	31-Dec-22
4	06-Feb-13		31-Dec-23		01-Jan-13	31-Dec-14	31-Dec-23
5	02-Feb-07	28-Oct-09	31-Mar-22		01-Jan-15	31-Dec-17	31-Dec-24
6					01-Jan-18	02-Aug-23	TBC
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=INDEX($G$2:$G$6,MATCH(1,(MAX($A2:$B2)<$F$2:$F$6)*(MAX($A2:$B2)>$E$2:$E$6),0))
F6	F6	=TODAY()

 

[bomijoff]

Consider posting some XL2BB in the future.

Anyway try:

Book89
	A	B	C	D	E	F	G
1
2	03-Jan-08	02-Feb-23	TBC		01-Dec-06	31-Dec-09	31-Mar-22
3	01-May-11	02-Feb-13	31-Dec-23		01-Jan-10	31-Dec-12	31-Dec-22
4	06-Feb-13		31-Dec-23		01-Jan-13	31-Dec-14	31-Dec-23
5	02-Feb-07	28-Oct-09	31-Mar-22		01-Jan-15	31-Dec-17	31-Dec-24
6					01-Jan-18	02-Aug-23	TBC
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=INDEX($G$2:$G$6,MATCH(1,(MAX($A2:$B2)<$F$2:$F$6)*(MAX($A2:$B2)>$E$2:$E$6),0))
F6	F6	=TODAY()

This is perfect and unbelieveable helpful to me, thank you so much.