Using a UDF with a Vlookup formula

[julhs]

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]

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

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4
5
6
7
8					qw	5
9
10
11					qw	7
12																qw
13					qw	11
14
15
16
17					Result
18					5
19					7
20					11
21
22					Element
23					2	7
24
Sheet1

Cell Formulas
Range	Formula
E18:E20	E18	=vbaVLOOKUP(P12,E6:F15,2)
F23	F23	=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]

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.xlsm
	A	B	C	D	E	F	G	H
1
2		Lookup value:		Country	Item		Results
3		France		France	Apple		Apple
4				Germany	Pear		Lemon
5	Data Validation	France		Italy	Banana		cheese
6	List			France	Lemon
7				Italy	Orange
8				sweden	mango
9				wales	leeks
10				Italy	Grapes
11				France	cheese
12
Sheet4

Cell Formulas
Range	Formula
B3	B3	=B5
G3:G5	G3	=vbaVLOOKUP(B3,D3:E11,2)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B5	List	=$D$3:$D$11