Hello, spreadsheet noob here. I tried to find a solution to this problem with ChatGPT, but all the formulas created by it don't work.

Basically what I need from the function is:

Check if any cell in the row in the columns D through H contains the text "Director" among other text.
If it finds "Director", it will return the corresponding value from that cell to the cell in the column C from the same row.
If it doesn't find "Director", it will check if any cell in that row contains "Administrator" among other text.
If it finds "Administrator", it will return the corresponding value from the first cell in the row that contains "Administrator" to the cell in the column C from the same row.
If it doesn't find "Administrator" either, it will just return the value from column D to the cell in the column C from the same row.

It should do this to every row and populate the entire column C.

Thanks in advance!