VLOOKUP-like function: Select case for a long list in VBA Excel

Instead of multiple If ... Then statements in Excel VBA, you can use the Select Case structure. But how does one perform this task efficiently if the case is a long list? For example, have a look the following data:

Code ID Girls Names
0001    Sophia
0002    Emma
0003    Olivia
0004    Isabella
0005    Ava
0006    Lily
0007    Zoe
0008    Chloe
0009    Mia
0010    Madison
0011    Emily
0012    Ella
0013    Madelyn
0014    Abigail
0015    Aubrey
0016    Addison
0017    Avery
0018    Layla
0019    Hailey
0020    Amelia
0021    Hannah
0022    Charlotte
0023    Kaitlyn
0024    Harper
0025    Kaylee
0026    Sophie
0027    Mackenzie
0028    Peyton
0029    Riley
0030    Grace
0031    Brooklyn
0032    Sarah
0033    Aaliyah
0034    Anna
0035    Arianna
0036    Ellie
0037    Natalie
0038    Isabelle
0039    Lillian
0040    Evelyn
0041    Elizabeth
0042    Lyla
0043    Lucy
0044    Claire
0045    Makayla
0046    Kylie
0047    Audrey
0048    Maya
0049    Leah
0050    Gabriella
0051    Annabelle
0052    Savannah
0053    Nora
0054    Reagan
0055    Scarlett
0056    Samantha
0057    Alyssa
0058    Allison
0059    Elena
0060    Stella
0061    Alexis
0062    Victoria
0063    Aria
0064    Molly
0065    Maria
0066    Bailey
0067    Sydney
0068    Bella
0069    Mila
0070    Taylor
0071    Kayla
0072    Eva
0073    Jasmine
0074    Gianna
0075    Alexandra
0076    Julia
0077    Eliana
0078    Kennedy
0079    Brianna
0080    Ruby
0081    Lauren
0082    Alice
0083    Violet
0084    Kendall
0085    Morgan
0086    Caroline
0087    Piper
0088    Brooke
0089    Elise
0090    Alexa
0091    Sienna
0092    Reese
0093    Clara
0094    Paige
0095    Kate
0096    Nevaeh
0097    Sadie
0098    Quinn
0099    Isla
0100    Eleanor

I put list of Code ID in column AA and list of Girls' Names in column AB. There's no way I will type the above list using the Select Case structure, so I use the following code to do the same task. It matches the partial text in column A and print the result in column E:

Sub Matching_ID()
.......................................
Dim ID As String, j As Integer, k As Integer, List As Integer
List = Cells(Rows.Count, "AA").End(xlUp).Row
ID = Mid(Cells(i, "A"), j, 4)
For k = List To 2 Step -1
    If ID = Cells(k, "AA").Value Then
        Cells(j, "E") = Cells(k, "AB").Value
        Exit For
    Else
        Cells(j, "E") = ""
    End If
Next k
.......................................
End Sub

Though the above code works fine, but it's really time-consuming. Is there a better way?