Lookup the data based on the Reporting date

Sorry guys, This question is not about designer in alteryx.

This question is formula in excel. After I run the workflow it will overwrite the sheet in template. After that I need to lookup the data to Main File.

I want my lookup to be dynamic. Every month the data change. For ex. I have data in January, In the month of Feb. data will change.

This is my Main Sheet.

This is my Lookup sheet, Every time I run the workflow the data will change.

So I have data in lookup sheet for the month of Feb. I want to dynamically lookup the data based on the month(Reporting Date) and column. The output something like this,

This is the output what I want,

I want something like If Condition, If the reporting date is equal to 2\29\2020 then lookup the data and post to Main File row of February-20 and If Condition, If the reporting date is equal to 3\31\2020 then lookup the data and post to Main File row of March-20

Thank you!

Output

Accepted answers

Laurap1228

Something like the attached should work.

You will need to Copy and Paste as Values on your Output Tab before the Alteryx Output overwrites with the next months numbers.

Sample.xlsx

All comments

T_Willins

Hi @RichardAlt,

Actually, I would recommend a combination Alteryx and Excel solution to this. Before outputting the data to your workbook either change your reporting date to match your Main Sheet's format or create an additional field with the date in the same format. For instance for 02/29/2020 use the formula "%B-%y" which will return the result "February-20". If you put this field before your Lookup Sheet data you can use either a vlookup or index-match formula on your Main Sheet.

Looking at how this is configured, though, if your Alteryx output is only a single month, your January-20 data will be zero when you run February-20's data unless you are hard coding (i.e. - copy/paste special values) each month after running the Alteryx workflow.

Laurap1228

Something like the attached should work.

You will need to Copy and Paste as Values on your Output Tab before the Alteryx Output overwrites with the next months numbers.

Sample.xlsx

RichardAlt

Hi @Laurap1228, Thank you!

What if I changed my output to Append instead of Overwrite? So if the sheet has Feb data for ex. and I run again the workflow it will append only the March and in that case the data in Feb and March are available in main sheet. Is that possible? That the lookup is in wide range? for ex. I have Feb 29 and March 31 data both must be lookup. Sorry for my bad English.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703