Return value when target is reached with condition

[Sheri]

Hi
I have columns that show the Week, Name, and Units. What I need is to identify the week when the target is reached by the specified name. Please refer to the table below. I've also attached a file. I prefer not to have a helper column as I already have a big file.
Week Name Units
1 A 4
1 B 2
1 C 8
2 A 2
2 B 10
2 C 16
3 A 5
3 B 3
3 C 8
4 A 11
4 B 13
4 C 16
Name: A
Target: 15
Week Reached Target: 4
In the case of "B" the week will be 2 and for "C" it's week 3
Thanks in advance for the help.

 

[Luke M]

I think you'll need a helper column, like in attached. If you think about it, a single formula would have to perform a SUM of every line and then compare them all.

 

[Sheri]

I have another tab that shows the list of names, their target, and Week when they reached their target. The week reached will be referencing to the data tab that has their weekly sales. I was able to find a formula that returns the week target is reached but do not know how to include the Name as part of the condition.

 

[Mike H..]

I have another tab that shows the list of names, their target, and Week when they reached their target. The week reached will be referencing to the data tab that has their weekly sales. I was able to find a formula that returns the week target is reached but do not know how to include the Name as part of the condition.

Hi,

I think that type of formula is known to a lot of us here but as you have observed it's simply finding the row in which the target is reached and not taking account of the name and I doubt it can be modified to do that.

The helper column is the simplest method for this and you have a solution for that. Another way would be VB code, do you want that?

 

[Luke M]

Cool trick in that formula.
How's this then?

 

[Sheri]

Luke you are a genius. This is exactly what I need. You are my Ninja Star.
Mike, I still have to learn how to use VB.

Thanks for your help.