Vlookup with a formula

[deepaprash]

Hello,

I have two tables:

Main Table                                                                                Lookup Table

Date                   Close Price                                                Quarter        Start Date       End Date

2019-12-09              200                                                  QDEC 2019     2019-10-01      2019-12-31

2018-03-15              215                                                  QMAR 2020     2020-01-01      2020-03-31

2020-01-25              275                                                  QMAR 2018     2018-01-01      2018-03-31

                                                                                         QJUN 2020      2020-04-01     2020-06-30

                                                                                         Q......and so on

I would like to add a new column to the main table that populates each row with "Quarter" from the Lookup Table.

End result should look like this:         

Main Table - Modified                                                                      

Date                   Close Price           Quarter                                                    

2019-12-09              200                  QDEC 2019                                             

2018-03-15              215                  QMAR 2018                                                

2020-01-25              275                  QMAR 2020

So in excel one could have used a vlookup with a formula if Date is after Start Date and before end date, lookup value in Quarter. How do you achieve that here.

Thanks

Deepa

[DiegoParker · Accepted solution]

Hi Deepa,

My solution would be to use the append tool and then a filter: 

[Date] >= [Start Date]
AND
[Date]<=[End Date ]

Find the workbook attached.

Hope this helps If does, can I ask you to mark it as the solution? this will help other users to find it and will allow us to close the thread. Many thanks!

Best,
Diego

[MarqueeCrew · Accepted solution]

@deepaprash ,

I recently published a Generate Date Rows macro (https://gallery.alteryx.com/#!app/Generate-Date-Rows/5e31f42a826fd30ffcbf2c14) that converts the lookup table to each of the days in the range.  You can simply join to the output of the macro and find your lookup.

Cheers,

Mark