vlookup not choosing correct values

Question

I am using "vlookup" function in vba, but the returned values aren't correct. Does anyone know why I am not getting the right row for the inputted string?

    Dim x As String

    Dim rg As Range
    Set rg = ActiveSheet.Range("B16:E20")


    Dim firstValue As String
    Dim secondValue As String
    Dim thirdValue As String

    x = "Violet"

    firstValue = Application.WorksheetFunction.VLookup(x, rg, 2)

    Debug.Print firstValue


    secondValue = Application.WorksheetFunction.VLookup(x, rg, 3)

    Debug.Print secondValue


    thirdValue = Application.WorksheetFunction.VLookup(x, rg, 4)

    Debug.Print thirdValue

firstValue = 74
secondValue = 75
thirdValue = 76

---

Cells being used for Vlookup (sorry, I wasn't sure how to bring a proper set of cells into here. Each name/number is in a separate cell)

Magenta 2 3 4
Violet 20 21 22
Burgundy 38 39 40
Sapphire 56 57 58
Turquoise 74 75 76

Answer 1

According to documentation, try adding the fourth argument:

firstValue = Application.WorksheetFunction.VLookup(x, rg, 2, False)

Answer 2

ok I found a solution for this. Not sure how it works, but there shouldn't be "WorksheetFunction" section in there:

firstValue = Application.VLookup(x, rg, 2, False)
firstValue = Application.VLookup(x, rg, 3, False)
firstValue = Application.VLookup(x, rg, 4, False)

This correctly returns the values in the row (i.e, 20, 21, 22)

Thank you!!

Edit:

Something to take note about: if you use "True" for the last parameter, the range will need to be in alphabetical order for it to work. See Parameter 4: range_lookup at the url for an example

https://excelmacromastery.com/vba-vlookup/