How to create a dependent dropdown list that is dependent on a cells name?

Hello,

I tried making a dependent dropdown list that depends on what's in cell and not another list; I am having difficulty.

On the D column, the rank "Officer" or "Enlisted" appears depending on what the user picks from Column A's dropdown for each member.

If column D displays "Officer", then I would like a dropdown list showing the "Officer List" (column I) to appear in E column as a dropdown list.
If column D displays "Enlisted", then I would like a dropdown list showing the "Enlisted List" (column H) to appear in E column as a dropdown list.

For example,
-if A4 is graded as "O3", then B4 will display "Captain" and D4 will display "Officer." I would like E4 to have a drop-down list that displays all the choices on the I column (Officer List).
-if A25 is graded as "E3", then B25 will display "Airman 1st Class" and D25 will display "Enlisted." I would like E25 to have a drop-down list that displays all the choices on the H column (Enlisted List).

Has anyone ever done something like this?

I've included an attachment.