Avoid the "#value" error when using a Vlookup with Scrypting Dictionary

Question

I am trying to use a VBA Vlookup that I found in the Question below but I keep getting the result #Value (and I am not the only one according to the comments).
Question: How to optimize vlookup for high search count ? (alternatives to VLOOKUP)

After putting the Function in VBA. I am using it in my sheet like a normal Vlookup: "=vbalookup(value,Range,Col)". I also tried as an array formula but it still doesn't work.

Does someone see why?

Function vbalookup(lookupRange As Range, refRange As Range, dataCol As Long) As Variant
    Dim dict As New Scripting.Dictionary
    Dim myRow As Range
    Dim I As Long, J As Long
    Dim vResults() As Variant

    ' 1. Build a dictionnary
    For Each myRow In refRange.Columns(1).Cells
        ' Append A : B to dictionnary
        dict.Add myRow.Value, myRow.Offset(0, dataCol - 1).Value
    Next myRow

    ' 2. Use it over all lookup data
    ReDim vResults(1 To lookupRange.Rows.Count, 1 To lookupRange.Columns.Count) As Variant
    For I = 1 To lookupRange.Rows.Count
        For J = 1 To lookupRange.Columns.Count
          If dict.Exists(lookupRange.Cells(I, J).Value) Then
            vResults(I, J) = dict(lookupRange.Cells(I, J).Value)
          End If
        Next J
    Next I

    vbalookup = vResults
End Function

Routine

Needs sample data and expected results. I suspect this solution isn't actually what you're looking for, and if we have sample data we can probably come up with something more suited to your needs. – Redouble

Do you have a reference set to the Microsoft Scripting Runtime library? – Weinberg

@Weinberg Yes I have – Routine

@Redouble I will edit my post with a sample soon then. I thought I don t need a sample as it could be used as a normal vlookup as well, was I wrong? – Routine

Are all the values in the lookup column unique? – Weinberg

Yes also, I have just updated a small picture to show the error – Routine

Since vbalookup returns an Array, on the worksheet you need to enter as an array formula CTRL-SHIFT-ENTER. – Shame

@Shame it is entered as an Array already (see picture) – Routine

Answer 1

Instead of blindly copying code from someone else, I highly suggest first learning what the code is doing. If you must blindly copy, read the comments first: How to optimize vlookup for high search count ? (alternatives to VLOOKUP)

Taking a stab at the problem with the code (beyond issues with design, syntax, indentation, etc) is that your function isn't returning a specific value:

    ReDim vResults(1 To lookupRange.Rows.Count, 1 To lookupRange.Columns.Count) As Variant
    For I = 1 To lookupRange.Rows.Count
        For J = 1 To lookupRange.Columns.Count
          If dict.Exists(lookupRange.Cells(I, J).Value) Then
            vResults(I, J) = dict(lookupRange.Cells(I, J).Value)
          End If
        Next J
    Next I

    vbalookup = vResults

Do you see how it is creating a 2d array, and returns this array as a result of the formula? Try adding a breakpoint before the end of the function, and take a look at what vResults is in the Locals window.

This means that, in order to actually use the function, it must be entered in the cell as an array formula.

To do this simply, press CTRL+SHIFT+ENTER instead of ENTER when entering the formula into the cell.

This comes with a strong caution though: not only do you already not know how this code is working, but you also likely don't know how array formulas work. You, and you alone are responsible for your calculations. If you are working on anything important whatsoever, then here be dragons and turn the other way.

Once you get to the point of understanding how this all works, you may have an easier time using the code.

Rightward

Thank you for the answer. You are right that I haven t understood totally the Function. Please check my edit on my question as it didn t work already as an array either (now I understand why it has to be an array though) – Routine

@Routine Then let me re-emphasize. HERE BE DRAGONS. Not a good idea to move forward if you can't understand the code. – Rightward