Returning Non-Zero Vlookup/match/index

I have a selection of data (This is an example I'm making from thousands of lines). I'm using code to take the 'Full Value' , if it starts with some select letter combinations "PRO" "BSA" or "PQR" and then pulling the first seven characters. I did this using IF() and LEFT() functions. The problem is many of the 'Full Value' items are under the same 'Case Number.' If this is the case they should have the same 'abbreviated value', even though the 'Full Value' is something random and doesn't start with "PRO" "BSA" or "PQR."

The formula I had for this was:

=IF(LEFT(C2,3)="PRO",LEFT(C2,7),IF(LEFT(C2,3)="BSA",LEFT(C2,7),IF(LEFT(C2,3)="PQR",LEFT(C2,7),VLOOKUP(A2,$A$2:$C$25,2,FALSE))))

The problem with the VLOOKUP section is that is pulls the first value and I can't re-arrange the data so if the first item is "Water" as in this example it returns zero or an incorrect value.

If the Full value does not begin with any of the three listen letters I need it to return the same "abbreviated value" for a matching "Case Number" that is non-zero. Also if there happens to be no matching "Case Number" it will return blank.

Here is an example of some code that is having issues. Column D shows the correct values highlighted in red are the two that are having problems. The formula is in column B. It won't; it won't let me attach the file

Excel.PNG