vlookup with dynamic field

[mingjue]

Hi I am trying to recreate the Excel vlookup function in Alteryx, but I can't figure out. Need some help. Thanks.

Here is vlookup I use in Excel : =IF(A2="Jan",VLOOKUP(B2,[Jan]Jan!$A:$D,C2+1,0),IF(A2="Feb",VLOOKUP(B2,[Feb]Feb!$A:$D,C2+1,0)))

Here are 3 sample excels files :

Jan and Feb files have detail sales information for each ID each metric ID.

 I would like to put Jan and Feb information into summary files.

Summary
Month	ID	Metric ID	Sales
Jan	AAA	1	10
Jan	AAA	2	20
Jan	AAA	3	30
Jan	BBB	1	20
Jan	BBB	2	30
Jan	BBB	3	40
Feb	AAA	1	100
Feb	AAA	2	200
Feb	AAA	3	300
Feb	BBB	1	500
Feb	BBB	2	600
Feb	BBB	3	700

Jan File

ID	1	2	3
AAA	10	20	30
BBB	20	30	40

Feb file 

ID	1	2	3
AAA	100	200	300
BBB	500	600	700

[Emil_Kos]

Hi @mingjue,

I have created a solution for you:

Keep in mind that you will need to adjust this formula to make it work fo you.

Please mark my post as a solution if this was helpful!

[mingjue]

Thanks @Emil_Kos 

I may mislead you from my last post. 

Actually I would like to get sales number from Jan and Feb files and put into summary file. 

In the summary excel file, I used the vlookup formula as below. But I can't figure out how to do it in Alteryx. (I just began to use Alteryx)

Month	ID	Metric ID	Sales
Jan	AAA	1	=IF(A2="Jan",VLOOKUP(B2,[Jan]Jan!$A:$D,C2+1,0),IF(A2="Feb",VLOOKUP(B2,[Feb]Feb!$A:$D,C2+1,0)))
Jan	AAA	2
Jan	AAA	3
Jan	BBB	1
Jan	BBB	2
Jan	BBB	3
Feb	AAA	1
Feb	AAA	2
Feb	AAA	3
Feb	BBB	1
Feb	BBB	2
Feb	BBB	3

[Emil_Kos]

Hi @mingjue,

can you provide an expected output?

It looks like you are using this tab to fetch the data from Jan tab if it is January data or Feb tab if this is the data from February.

In Alteryx there is 2 vlookups tool. One is called the join tool and the second one is to find and replace. 

If you will provide us with more details we will definitely help you. 

[Emil_Kos · Accepted solution]

Hi @mingjue,

Actually, I was thinking about your requirement. I think I know what you need:

The output:

[mingjue]

Great, @Emil_Kos    It works. Thank you for quick response. 

 I am wondering any other solution which I don't need to combine Jan and Feb files and transpose combined file.  

[Emil_Kos]

Hi @mingjue,

Can you give me more details about why you are searching for alternative solutions? 

If all those files are in the one folder you can use wildcard in input tool to load all of them at once but I am not sure about removing transpose tool.