VLOOKUP Function with IF Condition in Alteryx

Hi Friends

I'm newbie to Alteryx. I'm struck to a VLOOPUP Function in Alteryx. I tried using Find & Replace and Join Function but it didn't worked. So, to start with I have 2 data sets which I want to marry up to get the new data in new tab created in file 1.

In excel i can simply use the condition IF(Strategy=",", VLOOKUP(Ticker, File 2!A:B,2,FALSE),Strategy)).

But how to use the same feature in Alteryx. I tried marrying both files with Find & Replace tool i was able to amend Ticker data in File 1 and replace with Strategy data of File 2. But faced 2 problems -

1) Where i see blank in excel my IF condition pick strategy from File 1.

2) I was not able to create new column in File 1 to apply the VLOOKUP. It was applied to existing columns.

File 1

Ticker	Strategy
PNL TICKER: PNL ENTITY: Collateral Funding	Collateral Funding
PNL Ticker: Reserves
PNL Ticker: Brokerage
	Options

File 2

Ticker	Strategy
NOTAPPLICABLE	VMASTER
CDXHY2206/19V4	HY22
CDXHY2312/19V3	HY23
NOTAPPLICABLE	VMASTER
CDXHY2206/19V4	HY22

Can someone please help me with the flow?

Best Regards

Vikas

Workflow

Data Investigation

Accepted answers

AlexSTeryx

Hi @Vikas27 ,
I added a line to File 1

In the attached WF I come to the same result:

Is it that you were looking for?
Best
Alex

VLOOKUP_Vikas27.yxmd

AlexSTeryx

Hi @Vikas27,

in the workflow you can see how I attached the data to your original data set.

Cheers

Alex

All comments

AlexSTeryx

Hi @Vikas27 ,
I'm not sure I got what you want. I rebuild first your example in Excel and there are no matches between File 1 and File 2:

Sorry fro the German Parameters in the formula (SVERWEIS = VLOOKUP, WENN = IF)
I'll add one data set, that creates a match and try to build a WF in Alteryx.
C U
Alex

AlexSTeryx

Hi @Vikas27 ,
I added a line to File 1

In the attached WF I come to the same result:

Is it that you were looking for?
Best
Alex

VLOOKUP_Vikas27.yxmd

Vikas27

Hi Alex -

Sorry, I gave bad data for Comparison. So partially yeah, so what i want is -

A) File 1 has ticker CDXHY2206/19V4 we have same in File 2 under Column header Ticker it should match and give Strategy as HY22 like you mentioned.

For Cases like PNL Ticker: Reserves in File 1, we have for same ticker Strategy mentioned as Reserves in file not captured in Sample data. It should give me as Reserves.

C) For the blanks in File 1 in Ticker tab like in Row wherever after VLOOKUP it see blank or Null with the IF Condition it should get the Strategy from File 1 like Options in this case.

And all these i need in new Column in my existing data set in File 1.

In nutshell if Ticker matches in both files give me Strategy details from File 2. If not, give me Strategy as mentioned in file 1.

Vikas27

@AlexSTeryx - Also, how can we get VLOOKUP on new column under same data set. I was not able to get thru Find and Replace feature.

AlexSTeryx

hi @Vikas27,

I apologise, but I've difficulties to follow you:

I do not understand what you mean in and C).

As for would you mind setting up an example in Excel as I did above?

Regarding C) I understand in cases where is a match in File 2 you want to get back "Strategy" from File 2, otherwise you will pick "Strategy" from File 1 and if this is empty / null the you want to see "options"? Is that correct?

Besides of that to my understanding the provided WF does exactly do what you described "in a nutshell". But obviously I got something wrong, since you are not yet happy with the solution.

Would you mind explaining again?

Best

Alex

binu_acs

@Vikas27 @It would be very helpful if you provide a sample test file with expected result

AlexSTeryx

Hi @Vikas27,

in the workflow you can see how I attached the data to your original data set.

Cheers

Alex

Vikas27

@AlexSTeryx - I tried using your WF i was close to result but not 100%. So, i've attached excel files for easiness

Input file has multiple Columns. I want Workflow to create Column F (Strategy Updated) and do a VLOOKUP basis Ticker and pick Strategy from Mapping tab wherever possible. Where there is no mapping like in example of Options in Input file Strategy is blank in that case Input file will not be able to locate anything from Mapping file. In that case it can give me Options as result from same Input file Strategy tab.

Sorry for the confusion earlier, hope this simplifies.

@binu_acs - Please share your expertise.

Mapping.xlsx

Input.xlsx

AlexSTeryx

Hi@Vikas27
currently I’ve only my mobile , so I cannot download the files and build a workflow.

As far as I do see from the files there is no case where Strategie is empty in input file and where is also no match in mapping file. Do you have such cases and if soo, what do you want to have in then column F : blank, [Null] or something else?

to write „Option“ in column F you‘ve only amend the formula in the WF so that it picks „Option“ from column B if „Strategy“ is „Option“.

I‘ll no access rot my Alteryx before Monday.

best

Alex

Vikas27

@AlexSTeryx Morning Alex, I used your initial WF where we used Find & Replace tool and did append to select new column. It worked however there were some null data which I filled by doing the mapping. Thanks for all your help ! Much appreciated.

Next I’m going to test the cross tab as I need to create pivot on my data set. Let’s see if I can build out something out myself.

AlexSTeryx

Good Morning @Vikas27 ,

thanks for accepting my suggestion as solution! Happy, I could help you :-).
Good luck with the cross tap! Keep posting in case you struggle ;-)
Best

Alex

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335