Advanced Find and Replace function or macro help

You could do this with a macro. I am going to assume that since you posted this under Worksheet functions you would prefer to do this with a function is you could.

Macro or function, I would start the same way. I assume the rules could change every day, so you do not want them "hard coded". Therefore I set up a workbook with one sheet for the data and another for the "rules".

I used logical array expressions with sumproduct to return the row number where both criteria are matched, or zero if no match. Then, used INDEX function to use the row number (if there is one), or the current GL code if no match was found.

You could combine the INDEX and SUMPRODUCT expressions to get a single formula. But, I suspect that this logic is challenging enough as it is for most folks.

Obviously, with this approach, you would need to do some manual work. Namely, replace the functions with their results (Edit >> Copy, then Edit >> Paste Special Values) and replace the current GL code column with the results in the final column.

If you prefer the macro approach, respond accordingly.


---------- ADDED LATER ----------------------

I should mention ... there will be an issue if ever a case arises where the data matches criteria ("rules") in more than a single row. Namely, since this is a SUMPRODUCT, it will return the SUM of the row numbers ... which is not what you want. I am thinking there will never be such a case. But, I need to warn you of that.

This could be re-written to take that possibility into account and return either the first match (minimum row number), or the last match (maximum row number). But, to do that, you could no longer use SUMPRODUCT (at least I am not smart enough to know how). In such a case you would need to use Array Formulas.

here's a macro for you - assumes your data is in columna a-h

Sub macro1()
firstrow = Cells(1, 1).End(xlDown).Row
lastrow = Cells(64000, 1).End(xlUp).Row
If firstrow = lastrow Then firstrow = 1
Columns("G:G").NumberFormat = "@"
For i = firstrow To lastrow
If Left(Cells(i, 3), 5) = "ST-51" And Cells(i, 7) = "6050-2-4" Then _
Cells(i, 7) = "6050-7-4"
If Left(Cells(i, 3), 5) = "ST-53" And Cells(i, 7) = "6040-3-5" Then _
Cells(i, 7) = "6040-7-5"
' add more conditions here if you need
Next i
End Sub