Pull data from reference tableon condition, like excel lookup in if statement

[Thomas19]

Hello everyone, 

I am struggling with reproducing a standard Excel scenario in Alteryx. 

I have two tables. A Data Table containing the following columns, intially with an empty "Target" column.

DATA TABLE

ID	Kind	Value	Target
1	a	11	10
2	a	13	14
3	b	19	20
4	a	17	18
5	b	15	16
6	b	17	16
7	a	3	2
8	a	5	6
9	b	5	4
10	a	7	6
11	b	9	8
12	a	15	14
13	a	1	2

The second table is the reference table, from which the target value should be pulled. "Target" depends on the kind and should then match the "Value" field.

REFERENCE TABLE

Value	Kind a	Kind b
1	2	1
3	2	4
5	6	4
7	6	8
9	10	8
11	10	12
13	14	12
15	14	16
17	18	16
19	18	20

In excel that would be something like:

=if(Kind="a";vlookup(Value;REF-TABLE;2);vlookup(Value;REF-TABLE;3))

How can I populate the Datea-Table / Target column with the correct values from the Reference Table?

Many thanks in advance! 

[AlanSchaeffer · Accepted solution]

Hi @Thomas19 ,

I'm not an expert in Alteryx but I've made a simple workflow that might help you on you're problem.

If you have any question please ask them.

Sincerely,
Alan

[Thomas19]

Hi Alan, many thanks that does the trick!