Request help with Formula

[duttaabhishek]

Hi I have a dataset as attached. I want to replicate the Excel Formula in Column "J" (Summary Tab) to Alteryx.  I am not able to how to use alteryx to get the desired result. Can someone please help. 

[ChrisTX]

The attached workflow should point you in the right direction.

IF Summary sheet cell G3 = API or Manufacturing:
INDEX('Lookup Tables'!AN:AN, MATCH(Summary!I3, 'Lookup Tables'!AO:AO, 0)):
This function searches for a value from cell I3 of the 'Summary' sheet within column AO of the 'Lookup Tables' sheet. If a match is found, it returns the corresponding value from column AN of the 'Lookup Tables' sheet.

IF Summary sheet cell G3 = any other value:
INDEX('Lookup Tables'!AM:AM, MATCH(Summary!I3, 'Lookup Tables'!AO:AO, 0)):
This function searches for a value from cell I3 of the 'Summary' sheet within column AO of the 'Lookup Tables' sheet. If a match is found, it returns the corresponding value from column AM of the 'Lookup Tables' sheet.

The "New column" below is after I deleted empty rows and columns from your original sheets.

Summary sheet:
  Original column G = New column E = 3rd Party Category Note: this is also a formula, you'll need to calculate the 3rd Party Category value first
  Original Column I = New column G = Payment Terms Note: this is also a formula, you'll need to calculate the Payment Terms value first

Lookup Tables sheet: (I copied the 3 columns below to a new sheet "Payment Terms Mapping")
  Original column AM = (sheet Payment Terms Mapping) New column A = Indirect
  Original column AN = (sheet Payment Terms Mapping) New column B = Direct
  Original column AO = (sheet Payment Terms Mapping) New column C = Payment Terms

 

Chris