How to replace values in one column based on matching criteria of other columns?

[kanety12]

Hello,

I am working on trying to create a dynamic workflow for a colleague. I am trying to find a way to create a formula that will help to replace values in column numbers based on matching characteristics. I have already transposed the data in my screenshot below and am trying to update data in the final 3 columns to reflect matches in other data. So, in the screenshot below, every instance of Column28 [Name] and GLSS [ Unit Submission] should have "GX00" as the unit code. Every instance of Column30 and GLSS should have "H101," and so on and so forth. There are 170+ columns for some of these unit submissions, and that can vary year to year for us. So, I am trying to make a dynamic formula that can adjust based on whatever data is possible. I can't change the [Value] column. While it has the information to populate those last 3 columns, it also contains the dollar information we are looking for. So currently I used a formula to populate the last 3 columns with the [Value] column. I want to replace all instances of "0" in those last 3 columns so that each state has the correct information with it.

Is anyone able to help with this?

Thank you in advance!

[Prometheus]

@kanety12 While using a conditional formula for some values is great, if you're trying to replace a whole mess of values (I say minimum of 5), I would use the Find Replace tool instead.