One approach

=IF(COUNTIF('Consumables Codes'!$B$1:$C$200,C309),INDEX('Consumables Codes'!$F$1:$F$200,LOOKUP(9.99999999999999E+307,CHOOSE({1,2},MATCH(C309,'Consumables Codes'!$B$1:$B$200,0),MATCH(C309,'Consumables Codes'!$C$1:$C$200,0)))),"")