# Using a UDF with a Vlookup formula



## julhs (Dec 23, 2022)

First and foremost I need to establish as to whether or not the below UDF combined with a Lookup is compatible with Excel 2010

UDF is from here,  Oscar Cronquist – "Get Digital Help"     




My Lookup formula in R12 is : ={vbaVLOOKUP(P12,E6:F15,2)}

UDF is this:-


```
'

'UDF along with a Vlookup formula to find & list multiple matches of selected value
'
Function vbaVlookup(lookup_value As Range, tbl As Range, col_index_num As Integer, Optional layout As String = "v")

         'Declare variables and data types

Dim r As Single, Lrow, Lcol As Single, temp() As Variant
         'Redimension array variable temp
ReDim temp(0)
         'Iterate through cells in cell range
For r = 1 To tbl.Rows.Count
          'Check if lookup_value is equal to cell value
If lookup_value = tbl.Cells(r, 1) Then
           'Save cell value to array variable temp
temp(UBound(temp)) = tbl.Cells(r, col_index_num)
           'Add anoher container to array variable temp
ReDim Preserve temp(UBound(temp) + 1)

End If
Next r

If layout = "h" Then
          'Save the number of columns the user has entered this User Defined Function in.
Lcol = Range(Application.Caller.Address).Columns.Count
          'Iterate through each container in array variable temp that won't be populated
For r = UBound(temp) To Lcol
           'Save a blank to array container
temp(UBound(temp)) = ""
            'Increase the size of array variable temp with 1
ReDim Preserve temp(UBound(temp) + 1)
Next r
             'Decrease the size of array variable temp with 1
ReDim Preserve temp(UBound(temp) - 1)
             'Return values to worksheet
vbaVlookup = temp
             'These lines will be rund if variable layout is not equal to h
Else
             'Save the number of rows the user has entered this User Defined Function in
Lrow = Range(Application.Caller.Address).Rows.Count
            'Iterate through empty cells and save nothing to them in order to avoid an error being displayed
For r = UBound(temp) To Lrow
temp(UBound(temp)) = ""
ReDim Preserve temp(UBound(temp) + 1)
Next r
             'Decrease the size of array variable temp with 1
ReDim Preserve temp(UBound(temp) - 1)
              'Return temp variable to worksheet with values rearranged vertically
vbaVlookup = Application.Transpose(temp)

End If
End Function
```


While the UDF works with the 1st Vlookup formula in R12 to give the 1st instance of the look up value and gives the expected value.
However the 2nd Vlookup formula in R13 that is supposed to give the 2nd instance of lookup value is giving me a repeat of the first, it’s the same for R14,R15………


----------



## StephenCrump (Dec 23, 2022)

Your code should work fine in 2010.  Here's how it works in 365:

ABCDEFGHIJKLMNOP12345678qw591011qw712qw13qw1114151617Result18519720112122Element232724Sheet1Cell FormulasRangeFormulaE18:E20E18=vbaVLOOKUP(P12,E6:F15,2)F23F23=INDEX(vbaVLOOKUP(P12,E6:F15,2),2)Dynamic array formulas.
Using 365, you don't need to array-enter, and the result in E18 "spills", so that all three matches display.  

In earlier versions such as 2010, you'll need to array-enter the formula, as you've done.  The result cell will display only the top left hand element of the array, i.e. you will see 5 in this case, but the cell will actually contain the complete vector {3;5;7}.

(I haven't looked at the video at all).


----------



## julhs (Dec 24, 2022)

Thank you for confirming that and your help Stephan.

That confirmed I had entered something incorrectly, so went back and re watched the video numerous times!!
This post was about trying/testing an alternative method to achieve what I was trying to do in a previous one, safe to say this one didn’t do what I was looking for, so further web browsing required!
Link to my previous Copy “Comments” using VBA, with a twist.

Found where I went wrong with VLOOKUP array!!
Being a knob head and not understanding what he meant in the video by “Repeat” or being conversant with arrays I entered the vlookup in G3 and entered it as array, then I simply COPIED G3 to G4 and entered it as an array. That was my mistake and why I was getting the same value in ALL the results.
On re watching, I realised I should have entered {=vbaVLOOKUP(B3,D3:E11,2)} in G3, then click on G3 again & extend the range down as many rows as is required, then click IN the Formula bar and enter using ctrl+shift+enter.

This particular UDF of Oscar Cronquist – “Get Digital Help” is case sensitive regarding the vlookup value, but I vaguely remember seeing one on his site that wasn’t.

Xl2bb of mock-up of his video version
Accounts 2016 - 2019 Final Currant.xlsmABCDEFGH12Lookup value:CountryItemResults3FranceFranceAppleApple4GermanyPearLemon5Data ValidationFranceItalyBananacheese6ListFranceLemon7ItalyOrange8swedenmango9wales leeks10ItalyGrapes11Francecheese12Sheet4Cell FormulasRangeFormulaB3B3=B5G3:G5G3=vbaVLOOKUP(B3,D3:E11,2)Press CTRL+SHIFT+ENTER to enter array formulas.Cells with Data ValidationCellAllowCriteriaB5List=$D$3:$D$11


----------

