Trying to get month name using a formula

[thp510]

Hello. I have a cell where the month may change based on the calendar quarter found in cell A1. See below.

	A	B	C
1	Q1
2	Jan	Feb	Mar

Based on what's in cell A1 (Q1, Q2, Q3, or Q4) I'm looking for the start of the month in that quarter to populate in cell A2. So, in this example, A1 is equal to Q1, therefore cell A2 shows JAN. If, cell A1 showed "Q2", then cell A2 would be April... and so forth. That is the easy part, however I'm trying to figure out how I can write a formula that will auto populate with the 2nd and 3rd months of the specific quarter found in cell B2 and C2. Any advice? I'd like cell B2 and cell C2 to be dynamic based on what's populated in A2 (which is populated based on what's in cell A1). Thanks for your help.

 

[bebo021999]

Book2
	A	B	C
1	Q1
2	Jan	Feb	Mar
Sheet1

Cell Formulas
Range	Formula
A2	A2	=DATE(2023,LOOKUP($A$1,{"Q1","Q2","Q3","Q4"},{1,4,7,10}),1)
B2:C2	B2	=EDATE(A2,1)

A2:C2 are actual date, formatted as "mmm"

 

[Peter_SSs]

If you just want the cells to appear to have that text but are happy for the underlying values to be dates (as is the case with the post 2 suggestion) then you could use the formula in A2. It will "spill" across the other two cells and you would format those cells as "mmm" as described in post 2

If you want the cells to actually be text rather than dates then try the A3 formula. Again it will spill right but you won't have to format the cells.

23 03 06.xlsm
	A	B	C
1	Q1
2	Jan	Feb	Mar
3	Jan	Feb	Mar
Months

Cell Formulas
Range	Formula
A2:C2	A2	=DATE(2023,(RIGHT(A1,1)-1)*3+SEQUENCE(,3),1)
A3:C3	A3	=TEXT(DATE(2023,(RIGHT(A1,1)-1)*3+SEQUENCE(,3),1),"mmm")
Dynamic array formulas.