Please help with function extracting specific data -attached workbook

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

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