Lookup dates based from lookup values

[agonysWeeper]

Hi-

Got this setup and trying to figure a better formula to get the correct results.
I wanted to run a lookup formula in Column C based from column B value (dates), formula must also rely in column A value which is quarters, I'm trying to run an IF and a lookup formula but since there are a lot of quarters, this seems not good.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18Q1[/TD]
[TD]3/1/2015[/TD]
[TD][/TD]
[TD]*result must be last Qtr[/TD]
[/TR]
[TR]
[TD]16Q1[/TD]
[TD]11/6/2014[/TD]
[TD][/TD]
[TD]*result must be last Qtr[/TD]
[/TR]
[TR]
[TD]12Q4[/TD]
[TD]6/3/2017[/TD]
[TD][/TD]
[TD]*result must be Next Qtr[/TD]
[/TR]
[TR]
[TD]17Q1[/TD]
[TD]2/2/2017[/TD]
[TD][/TD]
[TD]*result must be This Qtr[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and the Quarters has 3 dates category something like these.
18Q1
1/1/1900 - Last Qtr
1/1/2018 - This Qtr
4/1/2018 - Next Qtr
18Q2
1/1/1900 - Last Qtr
4/1/2018 - This Qtr
7/1/2018 - Next Qtr
and soon.

 

[Rijnsent]

Hi agonysWeeper,
If I get your example, you're trying to see if the date in column B is in the quarter in column A, right? If so, you'd first have to transform your column A into a date, e.g. like so:

=DATE(2000+LEFT(A2,2),RIGHT(A2,1)*3-2,1) -> first day of the quarter
=DATE(2000+LEFT(A2,2),RIGHT(A2,1)*3+1,0) -> last day of the quarter

Next, compare them in a double IF function:

=IF(B2 < DATE(2000+LEFT(A2,2),RIGHT(A2,1)*3-2,1),"last", IF(M14 > DATE(2000+LEFT(A2,2),RIGHT(A2,1)*3+1,0),"next","this"))


Hope that helps,

Koen