vlookup not choosing correct values

asked 2021-04-07T13:53:09.033+00:00
ConS10 1 Reputation point

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

No comments
{count} votes

2 answers

Sort by: Most helpful
  1. answered 2021-04-07T16:34:32.77+00:00
    Viorel 82,286 Reputation points

    According to documentation, try adding the fourth argument:

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

  2. answered 2021-04-07T19:02:36.003+00:00
    ConS10 1 Reputation point

    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/

    No comments