Need to return column header when a value is found in a table in different worksheets.

[ironmaiden666]

I work for a B2B tech company. I have a database of business contacts with their company names, phone numbers and other contact information (Sheet 1). I have separate files which has details about companies and what each is looking to purchase technology-wise. I want to merge these files into one file in such a way that I can see against my contacts, what technology solutions their companies are planning to buy. This will really help sales efforts.

The underlying logic I think I need to use is this: "If [Name of Company] is found anywhere in a table with multiple columns in Sheet 2/3/4, return the column header in sheet 1". I will demonstrate in a table below what I'm looking for. I need to fill in the italicized parts:

Sheet 1

[TABLE="width: 600"]
<tbody>[TR]
[TD]Contact Name[/TD]
[TD]Company[/TD]
[TD]Phone[/TD]
[TD]Email[/TD]
[TD]Company Revenue (from sheet 2)[/TD]
[TD]Agent Analytics (from Sheet 3)[/TD]
[TD]Cloud Solutions (from Sheet 4)[/TD]
[/TR]
[TR]
[TD]Person 1[/TD]
[TD="align: center"]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]$10bn+[/TD]
[TD]Immediately[/TD]
[TD]6-12 months[/TD]
[/TR]
[TR]
[TD]Person 2[/TD]
[TD="align: center"]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]$10bn+[/TD]
[TD]6-12 months[/TD]
[TD]Immediately[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 - Company Revenue

[TABLE="width: 600"]
<tbody>[TR]
[TD="align: center"]$10bn+[/TD]
[TD="align: center"]$5-10bn[/TD]
[TD="align: center"]$1-5bn[/TD]
[TD="align: center"]$500mn-1bn[/TD]
[TD="align: center"]<500mn[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]E[/TD]
[TD]K[/TD]
[TD]P[/TD]
[TD]V[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]F[/TD]
[TD]L[/TD]
[TD]Q[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]G[/TD]
[TD]M[/TD]
[TD]R[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]H[/TD]
[TD]N[/TD]
[TD]S[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]I[/TD]
[TD]O[/TD]
[TD]T[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]J[/TD]
[TD][/TD]
[TD]U[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 3 - Agent Analytics Solutions

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Immediately[/TD]
[TD="align: center"]6-12 months[/TD]
[TD="align: center"]12-24 months[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]F[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]U[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]S[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 4 - Cloud Solutions

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Immediately[/TD]
[TD="align: center"]6-12 months[/TD]
[TD="align: center"]12-24 months[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD="align: center"]H[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]P[/TD]
[/TR]
[TR]
[TD="align: center"]W[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]T[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]F[/TD]
[/TR]
</tbody>[/TABLE]

Can someone please help me with this? I have tried so many options but I keep making mistakes