Assign formula based on a lookup table

[Fizzledrizzle]

Howdy Everyone,

I am having difficulty figuring out what terms to search for to get me what I am looking for. I have two sets of input: one is an account list with data colums, and the other is an account list with two columns that I need populated in a specific way based on the account. Words are hard, here is a picture:

in the above example I would want:

account 1's Field_1 to be 4 (Data_a) and Field_2 to be 9 (Data_d)

account 2's Field 1 to be 4 (Data_b) and Field_2 to be 1 (Data_e + Data_a)

in reality my formulas would be more complex than this, but the above is provided for simplicity.

Any help would be really appreciated. 

Thank you

[Qiu]

@Fizzledrizzle 
I think the Dynamic Replace Tool might work for your case.

Can you upload the sample two input data?

[Fizzledrizzle]

@Qiu 

Sure, here is the sample data and the workflow I used for the above screenshot.

[Qiu]

@Fizzledrizzle 
Took me quite a while, but I think this might what you need.

I found this one is very interesting and thinking about submitting it as an idea for the weekly challenge.

What do you think?

[patrick_digan · Accepted solution]

@Fizzledrizzle  @Qiu I would use the dynamic replace like this:

[Fizzledrizzle]

@patrick_digan 

Your solution worked really well and was really easy to follow.

Thank you @Qiu for working on this as well.

Thanks for helping me solve this!

[Qiu]

@patrick_digan 
Nice one.
I thought it can only be solved by macro. Thank you! 😁