Importing data to new column, with cell contents filled based on existing column data

Sorry if my title is a bad description - I'm finding it hard to briefly explain what I am trying to achieve (which isn't helping me search for useful terms).
Attached is a small example spreadsheet with my two existing sheets, and what I would like to achieve.

I have data in one sheet which has two relevant identifiers ('ID' and 'ScientificName' - these are consistent and linked). Each ID/ScientifiName might have several Threat Codes, so can appear in multiple rows in this sheet.

I want to add the 'Assessment' data from a different sheet - here each ID/ScientificName appears only once.

Can I pull in the 'Assessment' data and somehow link it to either 'ID' or 'ScientificName' so that the correct 'Assessment' data will appear in a new column, including the repeats where they occur...?

Thanks in advance for any help with this! Even any help on what terms might be useful for me to search would be appreciated!

In D2, you can copy this formula and then copy it down the list:

=INDEX(Assessment!$C$2:$C$5,MATCH(ThreatCode!A2,Assessment!$A$2:$A$5,0))

Thank you, that's perfect - I knew there would be a way!!

Hence, why the formula given in Post #2 HAD the $ signs already in it