I've got a seemingly involved request that I can't seem to wrap my head around. I will try and simplify as best I can...
I have a column (A) that contains number values, and a column (B) that contains text. I would like to make a formula that will compare the last 5 characters of the string in (B) to another column (C), and if it finds that the last 5 from (B) match the characters in (C), then a value in the cell adjacent to the matching cell in (C) (In another column, (D)) will be added to (A).
So, for example, if I start ABCD^-EF in (B1), I want to match D^-EF to a value in for example (D1-D20) (D1 being for example A^-EF, D2 being B^-EF, etc). If the last 5 match one of the values in (D1-D20), I would then like to add a value directly adjacent to the matching string in (E1-E20) to be added to my value in (A1). So, to complete that example, if the value in (B1) is ABCD^-EF, the value in (D4) is D^-EF and therefore matches the last 5 of (B1), the value in (E4) is 10, so I would want 10 added to (A1).
Essentially what I am trying to do is associate the last 5 characters with a value, and then add that value whenever those 5 characters end an associated string. I hope that makes some sense, I think that's the best I can describe it.
If anyone has any ideas on this one, I'd really appreciate it. I think it will be possible to do within Excel, so I'd like to avoid using a script if I don't have to.