I need a function that will extract ONLY the independent directors' "time between appointment and offering"

I made an additional column underneath to show you how it's meant to look like (I got their numbers manually)

Need it to be automatic. I cannot do it manually as there are over 1000 rows to do.
Maybe Vlookup? I dont know how to do it though.

Why does the extraction start at column AU instead of column L?

Because I just want those who have position with the word independent director in it

OK, saw that after I posted. But given the limited sample it's impossible to give a proper answer. This does it for the sample you gave, though.

In cell B18: =INDEX(5:5,MATCH("*Independent Director",5:5,0)+4)
In cell C18 (and dragged over): =INDEX(5:5,MATCH("*Independent Director",5:5,0)+COLUMN(A:A)*7+4)

ohhh thank you!!
can you explaing the column a:a thing?
what does that do exactly

Oh and i have another question.
the results are supposed to be displayed on a different sheet, so how would the function look like on another sheet referring to that one?

As you drag across the COLUMN(A:A) increases by 1 so that after the multiplication by 7 you increase by 7 the first time, then by 14, then by 21 etc.

It hinges on the sample's inference that all independent directors follow the first one found. If some other title is thrown in between them then that person's value will be returned.
A test can be added to the formula that will return a blank cell after exceeding the total # of independent directors. I don't know if you need that or not?

there are 8 columns,
if there are only 2 directors, i'd like the other 6 to be blank

Also, how can I put this formula on the second sheet? Because it's meant to be on another sheet

As for your second question: add the sheet name reference in front of both instances of 5:5 - like so: Sheet1!5:5 or 'Sheet One'!5:5 (apostrophes needed for names having space(s) in them)

=INDEX(Sheet1!5:5,MATCH("*Independent Director",5:5,0)+4)

like this?
it returns N/A

Use this formula on another sheet to find the 2nd occurrence - and drag across:

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

=INDEX(Sheet1!5:5,MATCH("*Independent Director",5:5,0)+4)
returns #N/A because you didn't
add the sheet name reference in front of both instances of 5:5
Note the word "both"

Thank you so much!!! You're a life saver

You're welcome. If that solves your issue please remember to mark your thread as SOLVED (instructions in rule #9 - click Forum Rules @ top of page to view).

Will do.
I have a question though. Is it possible to do this referring to two sheets?
So lets say the first sheet has 5 independents.
But the 2nd sheet (for the same company) has 2 independents.
Is it possible to include sheet2 in the function??

Sounds like it would be possible but much more complicated.

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