How can create a MACH in VLOOKUP function in Alteryx

[AlanoudF]

If we have if Excel

VLOOKUP( look up value, table array,MATCH( look up value, table array,0),0)

what tool in Alteryx we can use for this Excel function to get the same output

[cjaneczko]

The join tool does this. Both sheets would be brought in as different inputs using the input tool. You would then use the Join tool to MATCH on the fields. 

[FrederikE]

Hey @AlanoudF,

Like cjaneczko said, the Join tool has a similar functionality, but "Find Replace" is even more likely to the VLOOKUP function.

[jfha97]

Hey @AlanoudF,

To echo in with some of the above responses, both Join and Find Replace will work similar to Index Match / Xlookup. Find Replace works almost exactly as the excel formulas, whereas the Join tool can behave differently. The join tool matches the fields, so unlike index match or Find Replace, it can return cartesian products. This means that if you are matching two records and you have multiple return values and other criteria, instead of having a 1:1 match you will have 1:1, many:one, one:many, many:many.

Just wanted to say this in case you use the Join tool and find that you have doubled or tripled your dataset!

Edit: Grammar

[AlanoudF]

But how can I keep the order of the Identifier as seme as the first data set after getting the output

[jfha97]

Two things, find and replace will typically maintain the order of records coming in, join will keep it as well. To ease your mind, you can sort your data and add a RecordID tool, then you can see if it kept the order.

I maybe misunderstood your question, but if you want to keep the same order, the tools should already do that. However, with the join tool, remember that you will have “fallout”. This means that unmatched records will be shown in the R and L output nodes. This is probably why you think the order is missing.

Let me know if this is the case, I’ll gladly answer any questions.