extract cell contents (words) from an adjacent cell (forumla)

[Dig Digger]

Hello, and thanks for your worthy responses.
I have a PDF (words and tables) 700 pages.
This has been converted successfully to an excel file.

I'd like help understanding how to extract a cell contents (words) adjacent to key search phrases.
For example, you'll see in the attached image, a table. A similar table appears on each page amongst paragraphs for the 700 pages or now the long excel sheet.
I'd like a formula (on sheet 2 of the file w/extracted data) that extracts each - in this case "Job Title" i.e. 'Technical Service Manager'
So "Job Title" appears around 700 times - I'm looking to extract a list of all the actual titles.

I believe I'm aware of how to extract a cell content, but am not sure how to do the above...
Thanks again for any help,
Cheers Dave

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB for providing small sample data to make it easier for helpers by not having to manually type out sample data to test with.

Is it something like this you are after?

Dig Digger.xlsm
	A	B
1
2	Job Title	Job Title 1
3	Job Type	Job Type 1
4	Location	Location 1
5	Job Title	Job Title 2
6	Job Type	Job Type 2
7	Location	Location 2
8	Job Title	Job Title 3
9	Job Type	Job Type 3
10	Location	Location 3
Sheet1

Dig Digger.xlsm
	A
1	Job Title
2	Job Title 1
3	Job Title 2
4	Job Title 3
5
Sheet2

Cell Formulas
Range	Formula
A2:A4	A2	=FILTER(Sheet1!B2:B1000,Sheet1!A2:A1000=A1)
Dynamic array formulas.

 

[Dig Digger]

My apologies, I will check out that reference. This formula worked a treat, I was off by a mile. Thank you. One other question whilst I have you. This may be a silly question, but the formula you posted and I copied to test it out is obviously repeated throughout the column. However, when I look at the first formula say A2 it appears as normal, but all the following repeats of that formula are greyed out, and if you click into the attention bar where it is greyed out the formula disappears... Can I ask what is the process used for copying the same formula cell after cell? Cheers Dave

 

[Peter_SSs]

This formula worked a treat,

Good news. Glad it worked for you.

This is a "dynamic array" formula. It only needs to be entered in a single cell and Excel works out how many rows and/or columns the results need to "spill" into. This feature was introduced in late 2018 and is only available in Microsoft 365.

You can find out some more about it here

 

[Dig Digger]

Excellent thanks so much... I genuinely appreciate this. Cheers Dave

 

[Peter_SSs]