Okay, so I have the following:
COLUMN A COLUMN B
Person A
1 - BREAK
11:30:00 00:05:00
12:30:00 00:04:00
1 - BREAK 00:09:00
As you can see, for each Person I have a list of times that breaks were taken and the length of those breaks, followed by a total time. I essentially want to be able to pick up the total for each person. One thing to bear in mind though is that not each total is exactly four columns down (as depends on the amount breaks taken). As such, I need a formula that will allow me to search for the second instance of '1 - BREAK' after each persons name and then return the total time. I have about 20 different people's names, all different.
I've tried to do this with vlookup or a combination of offset and match/index but realise the second instance of something can't be picked up with vlookup therefore a bit lost as to what I could try next.
Is what I want to achieve actually possible?
Thanks.
Assuming your data is in A1:B20 and your lookup person is in E1, then this formula:
would get the value in column B where the second "1 - BREAK" occurs in column A for the respective person.=INDEX(INDEX($B$1:$B$20,MATCH(E1,$A$1:$A$20,0)+2):$B$20,MATCH("1 - BREAK",INDEX($A$1:$A$20,MATCH(E1,$A$1:$A$20,0)+2):$A$20,0))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Just perfect. Genius. Thanks so much for this. Reputation added...
Okay, this is working great, as stated above, but what if the "1 - BREAK" section is not directly after the person's name; say it could be the fourth category in the list? Can the formula also pick it up then?
Assuming same parameters, try:
=INDEX(INDEX(INDEX($B$1:$B$20,MATCH(E1,$A$1:$A$20,0)):$B$20,MATCH("1 - Break",INDEX($A$1:$A$20,MATCH(E1,$A$1:$A$20,0)):$A$20,0)+1):$B$20,MATCH("1 - Break",INDEX(INDEX($A$1:$A$20,MATCH(E1,$A$1:$A$20,0)):$A$20,MATCH("1 - Break",INDEX($A$1:$A$20,MATCH(E1,$A$1:$A$20,0)):$A$20,0)+1):$A$20,0))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Excellent, this is exactly what I wanted. One final question, however - could it work backwards? So, for example if Person A was underneath the 1 - BREAK section; is there a way of picking it up that way around too?
If it worked backwards though, it would be the first instance that I wanted to pick up, not the second - sorry, should have mentioned that.
Okay try:
This one needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER to work. You will see { } brackets around it.=INDEX($B$1:INDEX($B$1:INDEX($B$1:$B$20,MATCH(E1,$A$1:$A$20,0)),MATCH(2,1/(INDEX($A$1:INDEX($A$1:$A$20,MATCH(E1,$A$1:$A$20,0)),0)="1 - BREAK"))-1),MATCH(2,1/(INDEX($A$1:INDEX($A$1:INDEX($A$1:$A$20,MATCH(E1,$A$1:$A$20,0)),MATCH(2,1/(INDEX($A$1:INDEX($A$1:$A$20,MATCH(E1,$A$1:$A$20,0)),0)="1 - BREAK"))-1)="1 - BREAK",0))))
Now, let's hope you don't ask for a diagonal direction or some helix patterned lookup
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Okay, this is absolutely perfect, however... I would like it to pick up the 1st instance upwards of "1 - BREAK" rather than the second.
I guess it is a matter of interpretation....
By first, I assumed you meant the first "1 - Break" to appear above the person's name (but after the previous person's name).....
Anyways, this will give the last "1 - Break" above the person's name... which hopefully is what you mean by 1st....
=LOOKUP(9.99999,SEARCH("1 - Break",INDEX($A$1:INDEX($A$1:$A$20,MATCH(E1,$A$1:$A$20,0)),0)),INDEX($B$1:INDEX($B$1:$B$20,MATCH(E1,$A$1:$A$20,0)),0))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks