# Finding the second instance of a value after a text string

1. ## Finding the second instance of a value after a text string

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.

2. ## Re: Finding the second instance of a value after a text string

Assuming your data is in A1:B20 and your lookup person is in E1, then this formula:

``Please Login or Register  to view this content.``
would get the value in column B where the second "1 - BREAK" occurs in column A for the respective person.

3. ## Re: Finding the second instance of a value after a text string

Just perfect. Genius. Thanks so much for this. Reputation added...

4. ## Re: Finding the second instance of a value after a text string

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?

5. ## Re: Finding the second instance of a value after a text string

Assuming same parameters, try:

``Please Login or Register  to view this content.``

6. ## Re: Finding the second instance of a value after a text string

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?

7. ## Re: Finding the second instance of a value after a text string

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.

8. ## Re: Finding the second instance of a value after a text string

Okay try:

``Please Login or Register  to view this content.``
This one needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER to work. You will see { } brackets around it.

Now, let's hope you don't ask for a diagonal direction or some helix patterned lookup

9. ## Re: Finding the second instance of a value after a text string

Okay, this is absolutely perfect, however... I would like it to pick up the 1st instance upwards of "1 - BREAK" rather than the second.

10. ## Re: Finding the second instance of a value after a text string

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....

``Please Login or Register  to view this content.``

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1