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

Question

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?

score 3 · Answer 1 · answered Jun 30 '16 at 16:13

3

You can use VLOOKUP in VBA:

Sub Matching_ID()

    Dim ID As String, j As Long, i As Long, k As Long, List As Range
    Dim sht As Worksheet, v

    Set sht = ActiveSheet
    Set List = sht.Range(sht.Cells(2, "AA"), sht.Cells(Rows.Count, "AB").End(xlUp))

    ID = Mid(Cells(i, "A"), j, 4)

    'returns match or an error value if no match
    v = Application.VLookup(ID, List, 2, False)

    sht.Cells(j, "E") = IIf(IsError(v), "", v)

End Sub

answered Jun 30 '16 at 16:13

Tim Williams

154,628
8
97
125

Thanks for your answer. But using your code makes my program run slower (about 109s for 3064 data) than Scott's code. Why didn't you declare variable `v` as `string` or something? You left it blank. Though it's slower, I do really learn a lot in your answer like `.End(xlUp)` and `IIf`. Anyway, in [this answer](http://stackoverflow.com/a/18769246/3397819) you said, quote: "In my experience Application.Match() is up to ten times slower than calling a function which uses a loop". I'm wondering, is there a better VBA code to perform this task without using Excel Function? – Anastasiya-Romanova 秀 Jul 01 '16 at 02:32
1

@Anastasiya-Romanova秀 It was left as a variant on purpose, as was mine in my code. if the vlookup or match does not find the value it will return an error. If the variable was declared a string the code itself would throw a type mismatch error. So we want it to be a variant so it can hole and error or a string. Then the IIF tests for the error before committing the value. – Scott Craner Jul 01 '16 at 03:26
@Anastasiya-Romanova秀 - it's difficult to guess what would be fastest without seeing the rest of your code, and knowing the relative size of your lookup list vs. the number of items you're looking up. If all of the "output" goes in one continuous range then a dictionary-based lookup with a single write back to the sheet at the end might give the best performance. – Tim Williams Jul 01 '16 at 03:37
@TimWilliams I improve my code based on your advice. It runs very fast faster (about 1s for 6562 data). Many thanks, but unfortunately I have to choose Scott's answer since I use his answer in my code. I hope you're okay with that. Once again, thank you so much. I learn a lot from both of you. – Anastasiya-Romanova 秀 Jul 01 '16 at 06:38

score 2 · Accepted Answer · answered Jun 30 '16 at 16:05

2

I like using Match when searching a single column:

Dim t
'try to find ID
t = Application.Match(ID, Range("AA:AA"), 0)
'if not found t will be an error so we test that
If Not IsError(t) Then
    Cells(i, "E") = Cells(t, "AB").Value
Else
    Cells(i, "E") = ""
End If

answered Jun 30 '16 at 16:05

Scott Craner

148,073
10
49
81

Thanks for your answer. Thanks also for letting me know about MATCH function. Your code makes my program 4x faster (about 7s for 3064 data). I'm curious, is there a better way? – Anastasiya-Romanova 秀 Jul 01 '16 at 02:25
1

@Anastasiya-Romanova秀 see this article on speed tests with match vs find vs arrays: https://fastexcel.wordpress.com/2011/10/26/match-vs-find-vs-variant-array-vba-performance-shootout/ it test the three methods and finds that with single column matching Application.Match turned out the quickest. – Scott Craner Jul 01 '16 at 03:14

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

2 Answers2

Linked