Find Highest Value Based on Condition

Hi all

In my file I have two numbers, can one category
New lines are added as needed by the users.

Col. A contains the "sector number" with column B indicating the "sub sector number".
Col. B always uses the value in col A as prefix and is ascending in number
Col. C shows whether the product is "NORM" or "SPEC" - in case of "SPEC" the number does follow it's own numbering logic.

What I would like to achieve is that the user can, via input box, enter the "sector number" and the macro should return the next available number in a message box under "NORM" conditions - please see attached file

Sample.xlsx

How would I code such conditions, bearing in mind that my list is several 1000 lines long

Thanks
FD