Create column of merged data formula

[Excelrookie86]

Hi All,
I have a MESS of data because of custom fields.
I have 4 projects each with their own unique ID fields for the Project Leadof each.
For example.
Core Workload has a data point that is called Custom field (Project Manager-👔) (the weird part is an emoji I use in the software)
And each of the other projects reports back a unique column.

So I end up with this mess.

Custom field (Project Lead 🧰)	Custom field (Project Lead-👔)	Custom field (Project Lead-👔)	Custom field (Project Lead-👔)

I tried to think of hlookup to report back the names together in a single column for easier reporting and pivot table but I don't know what to do for the formula.

The goal is to be able to create a formula that will look for the term "Project lead" across an entire spreadsheet and then spit the resulting info into a column (preferably in a separate sheet within the same workbook.
Bonus points would be if the formula can ignore any columns that have Id in them as it spits back numbers and will create more issues. example below.

**Custom field (Project Lead🧰)Id

 

[Excelrookie86]

Let me know info/data you need if any.