Getting type mismatch when looking up string in array

I have some code that pulls a string from a spreadsheet and looks up the string's index value inside an array. I noticed if I keep my strings to 1 character, the code works fine. If I actually try to run it looking up words, I get a type mismatch error, even though the string DEFINITELY exists inside the array. Any help would be appreciated. I've pasted a dummed-down version of the code below that still causes the same error.

I've tried dim-ing my array as variant or as string. I've tried using white-space to make all strings in the array the same length.

Sub myArray_ISbroke()

    Dim arraysSuck: arraysSuck = Split("HI,HELLO,TEST1,TEST2,T3", ",")
    MsgBox Application.Match("HI", arraysSuck)
    MsgBox Application.Match("HELLO", arraysSuck)
    MsgBox Application.Match("TEST1", arraysSuck)
    MsgBox Application.Match("TEST2", arraysSuck)
    MsgBox Application.Match("T3", arraysSuck)

End Sub

I'd expect MSG boxes to come up with 1, 2, 3, 4, and 5. I get an error 13 after '1'.

Solution

You are missing the last parameter, the match type. See the docs here.

It says:

If match_type is 1, Match finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, Match finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order. Note that Match is case-insensitive.

If match_type is -1, Match finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

If match_type is omitted, it is assumed to be 1.

Revised code:

Option Explicit

Sub UpdatedExample()

    Dim arraysSuck: arraysSuck = Split("HI,HELLO,TEST1,TEST2,T3", ",")
    MsgBox Application.Match("HI", arraysSuck, 0)
    MsgBox Application.Match("HELLO", arraysSuck, 0)
    MsgBox Application.Match("TEST1", arraysSuck, 0)
    MsgBox Application.Match("TEST2", arraysSuck, 0)
    MsgBox Application.Match("T3", arraysSuck, 0)

End Sub