Achieve Nested Formula function (Vlookup, IF, IFERROR) in Alteryx

[LEXQ2005]

I have 3 data sources, as below:

I want to achieve below in Alteryx

Step 1: Keep all Request # in Record 1 file

Step 2: Add New Request # from Record 2 file

Step 3: Add a column to flag which Request# are “New Request” (In Record 2 file, but not in Record 1 file). (In Excel I can use this formula to achieve that: = IFERROR(IF(B3=VLOOKUP(B3,'Record 1'!A:A,1,FALSE),"","New Request"),"New Request"))

Step 4: Lookup Request # Status in Record 2 file (In Excel I can use this formula to achieve that: =IFERROR(IF(VLOOKUP(B10,'Record 2'!A:B,2,FALSE)=0, "(Status blank)", VLOOKUP(B10,'Record 2'!A:B,2,FALSE)),"Request # Not in Record 2"))

Step 5: Lookup Status in Work Ticket file (In Excel I can use this formula to achieve that: =IFERROR(VLOOKUP(B3,'Work Ticket'!B:C,2,FALSE), "Request # Not in Work Ticket"))

And the Alteryx output table is like this:

[bpatel · Accepted solution]

hi @LEXQ2005 ,

here is what i came up with. Need to use a combination of the formula and join tools. 

Hope this helps!