How do I convert this excel formula into Alteryx: =IFERROR(VLOOKUP(E2,'K:\Reports\Template

How do I create/ transform this column in Alteryx.

=IFERROR(VLOOKUP(E2,'K:\Reports\Templated reports\[Task Report - Active Template.xlsx]RR and OFF'!$A:$E,5,FALSE),VLOOKUP(E2,'K:\Reports\Templated reports\[Task List Report - Completed Template.xlsx]RR and OFF'!$A:$E,5,FALSE))

Transformation

Accepted answers

T_Willins

Hi @Kinetic_analytics,

It will be easier to give a more precise answer with sample data from the reports, but the reports can be brought into an Alteryx workflow and the correct fields (columns) can be compared using a Join tools. The data in column E from the first report (where cell E2 is located) is first compared to column A of the Active Template and the J output shows the data from the Active Template in column E; then the data that wasn't found in the Active Template (L output of first Join tool) is compared using the same process in the second Join tool. The data is then Unioned together. I added the RecordID, Sort and Select tools to show how the data could be brought back into its original order.

Join tool.PNG

Join data.yxmd

All comments

HomesickSurfer

Hi @Kinetic_analytics

Would it be acceptable to output as .xlsx, appending or replacing you records to/in a mapped range while still retaining your formula in adjacent column cells?

Kinetic_analytics

Yes.

I want this formula in a calculated new column. I have to create similar 15 new columns on the automation process.

Kinetic_analytics

yes

T_Willins

Hi @Kinetic_analytics,

Join tool.PNG

Join data.yxmd

Kinetic_analytics

Great help.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703