How to return the next value in a row based on whether it is already used in a list

[cTennant2]

Hi I have 2 sheets as below, In sheet 1, when a user selects 'RED' in ID type, they will be prompted to enter 'original ID number'.   I then need it to check the 'original id number' against the 'ID sub numer' column in Sheet 2 and populate the next available ID sub number , that HASN'T been used already in ID column of sheet 1, into the ID column of sheet 1. Does anyone know if this is possible please?    Or if there is a better way to format the sub-number lookup data to achieve this. 

Thanks for any advice:-)

SHEET  1
ID NUMBER	ORIGINAL ID NUMBER	ID TYPE
ID0001		BLUE
ID0002		BLUE
ID0003		BLUE
Want it to return ID0002-1	ID0002	RED
Want it to return ID0002-2	ID0002	RED
SHEET 2
ID sub number
ID0001
ID0001-1
ID0001-2
ID0001-3
ID0002
ID0002-1
ID0002-2
ID0002-3

[#2]

@cTennant2 

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    Dim s As Long
    Dim v As String
    Dim w As String
    Dim p As Long
    Dim n As Long
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Range("B2:C10000"), Target) Is Nothing Then Exit Sub
    r = Target.Row
    v = Range("B" & r).Value
    If v = "" Or Range("C" & r).Value <> "RED" Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For s = r - 1 To 2 Step -1
        w = Range("A" & s).Value
        If w Like v & "*" Then
            p = InStr(w, "-")
            If p Then
                n = Mid(w, p + 1)
            End If
            Range("A" & r).Value = v & "-" & n + 1
            Exit For
        End If
    Next s
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

[#3]

Hi@HansVogelaar 

Thanks you so much for your reply, its really appreciated. I'm restricted from using macros however and trying to achieve this with index and match formulas if at all possible.  Do you know if this is possible, i've tried lots of combinations but just can't seem to get it right. Many thanks:-)

[#4]

@cTennant2 

Enter the starting value ID0001 in A2.

In A3:

=IF(C3="RED", LET(v, XLOOKUP(B3&"*", A$2:A2, A$2:A2, "", 2, -1), B3&"-"&IFNA(TEXTAFTER(v, "-"), 0)+1), "ID"&TEXT(MAX(--MID(A$2:A2, 3, 4))+1, "0000"))

Fill down.