Find cell with value from a column and insert formula in the cell to its left

I uploaded an attachment to conceptualize it. What is needed is a macro that:
1. will run across all the sheets
2. find the cell that has any of the names from the table in sheet1 and
3. in its adjacent cell (colored in red) insert a vlookup formula that will put the respective id from the table.

The main problem is that the name is not in the same cell across different sheets :/