Lookup formula

[mattbird]

Hi,

I’m currently using the following formula excel 2016 (in cell G7) to identify what quarter a date fall into:

=IF($F7=“”,”NA”,(LOOKUP(MONTH($F7),{1,4,7,10},{“Q4”,”Q1”,”Q2”,”Q3”})))

For example:

1 Jan - end Mar = Q4
1 Apr - end Jun = Q1
1 Jul - end Sep = Q2
1 Oct - end Dec = Q3

How can I adapt the formula to also return the year ie

Date entered (cell F7): 01/04/23
Answer (cell G7): Q1 - 2023

Or

Date entered (cell F7): 01/04/24
Answer (cell G7): Q1 - 2024

And so on etc.

Any help is greatly appreciated.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or 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’)

Maybe

=IF($F7="","NA",LOOKUP(MONTH($F7),{1,4,7,10},{"Q4","Q1","Q2","Q3"})&"-"&YEAR($F7))

 

[mattbird]

What version of Excel are you using?

I suggest that you update your Account details (or 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’)

Maybe

=IF($F7="","NA",LOOKUP(MONTH($F7),{1,4,7,10},{"Q4","Q1","Q2","Q3"})&"-"&YEAR($F7))

Worked perfectly, thanks.

 

[Fluff]

You're welcome & thanks for the feedback.

Thanks also for updating your profile.