Complex Comparison forumula - Help

Ok, now that I understand the tests better, hopes for a simple formula have evaporated.

I reformatted your Prefix Table sheet so the data is complete and in standard format table. With that in place, we have to test each code to see if it's a 2-prefix match, a 3 prefix match, or then separately test if its CHS and check the numbers that follow that prefix.

=IF(ISNUMBER(MATCH(LEFT(A2,2), 'Prefix Table'!$B$2:$B$7, 0)), B2=300,
IFERROR((INDEX('Prefix Table'!$C$8:$C$13, MATCH(LEFT(A2, 3), 'Prefix Table'!$B$8:$B$13, 0))=B2),
IF(LEFT(A2,3)="CHS", IF(MID(A2,4,4)+0<168, B2='Prefix Table'!$C$14, B2='Prefix Table'!$C$15))))