Hello,

I am stuck with a problem I have with a spreadsheet, attached is some example data.

In column E there are cells that have entries like this "2965:3372:3117:3168:3314:2936" what I need to do is check each code separated by ":" by looking it up in column A, then checking in column D of that row, If that cel in column D for that row is empty then it is correct though if that cell in column D for that row contains "yes" or "no" then I need it to be able to check column B for the product group (with the same product titles) then return the reference found in column A for the row of that group that has nothing in column D and swap the code in the original list (2965:3372:3117:3168:3314:2936) with this new product code ID.

I hope this makes sense as I am desperate to be able to do this. I cant think of any way to do it with my basic formula and function knowledge.

As an example the cell E3 in the attached example data reads: "1965:1970:2933" this would change to "1962:1968:2929".

I'm very grateful for any help and hope to hear from someone soon.

Thanks,

Paul

example-data-variant-issue.xlsx