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.

  • I'd look at if and right but you won't be able to add to A1 if A1 has a number in it already – Raystafarian Jan 15 '16 at 16:19
up vote 0 down vote accepted

What you want is a vlookup function like this in column E:

=VLOOKUP(RIGHT(B1,5),C:D,2)+A1

The vlookup will search column c for the last five characters from B and then return the matching value in D. That value is then added to the value in column A.

  • Awesome, I can work it out from here, this is what I needed. – greyeyedsniper Jan 15 '16 at 16:42

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.