Lookup a value in another column

[RichW123]

Hi,

I wish to check whether a string in one column is present in another column. I've got a field titled "Home Matrix Organization Code" which I wish to check if the value exists in a separate column titled "Employee ID". If the value is present, I'd like to keep that value there and if it's not I'd like it to return null or empty.

I've attached dummy data in the attached file and the expected output in the adjacent columns

Thanks

[Felipe_Ribeir0]

Hi @RichW123 

You can use the find replace tool to do this:

[DataNath]

Hey @RichW123, here's one option I can think of where we use the data as a lookup for itself with the Find Replace tool. We basically check for the presence of the Employee ID in the Home Matrix Organisation Code field. If matches are found, it appends the Home Matrix Organisation Code next to it and nulls cells if not. This field with the part-populated, part-null records becomes our new Home Matrix Organisation Code field. Example attached for you to look over! Note: for the FR tool I had to temporarily make the fields strings, which I switched back in the final step.