Excel - Searching Multiple Columns

[JMcNamara]

I am trying to write an equation that will pull the officer name using the corresponding code to match. Below are a mock up of the data that I am using.

 

So what I need is a formula that will look at the "Code" column and search the "code type" spreadsheet, grab the applicable officer and put their name in the "officer" column on the second spreadsheet automatically.

The issue that I am having is I cant find a way for the equation to search for a match in all three "code types" columns and then pull the appropriate officer name.

[#2]

@JMcNamara 

In D2:

=INDEX('Code Types'!$A$2:$A$4,SUMPRODUCT(('Code Types'!$B$2:$D$4=C2)*(ROW('Code Types'!$B$2:$D$4)-ROW('Code Types'!$B$2)+1)))

where Code Types is the name of the sheet with Code Type 1, Code Type 2 etc.

Adjust the ranges if needed, then fill down.

[#3]

Worked great! Thanks! I truly appreciate your help.