Solved

How do I return a VBA function lookup value using 3 criteria ?

Posted on 2013-11-29
10
453 Views
Last Modified: 2013-12-01
Hi All,

I'm trying to create a VBA function which will do a VLOOKUP using 3 criteria on an Excel table and return a value.

I've attached a test workbook (no VBA code yet) with the lookup table and 3 dropdown cells which provide the input for the VLOOKUP function. On the change of any one of the dropdown cells, the function should return a result depending on the values in the dropdowns.

Not sure where to start with this.

Thanks in anticipation
Toco
Test30-Lookups-Multi-Criteria.xlsx
0
Comment
Question by:Tocogroup
  • 5
  • 4
10 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39685027
=SUMPRODUCT(PricesBasicTable[[Half day]:[Full day]]*(PricesBasicTable[Course]=G3)*(PricesBasicTable[Venue]=G4)*(PricesBasicTable[[#Headers],[Half day]:[Full day]]=G5))
0
 

Author Comment

by:Tocogroup
ID: 39685100
Wow ! That's impressive.
How do I convert that function into my VBA application ?
0
 
LVL 14

Assisted Solution

by:Faustulus
Faustulus earned 250 total points
ID: 39686381
In programming the function you requested I paid particular attention to make it easily adaptable to any changes you might yet make in your worksheet design. For example, I didn't take advantage of the fact that the columns for Half and Full day prices or the cells of your drop-downs are adjacent to each other. You can move your rows and columns, your drop-downs and the result cell as you wish. All the code requires is for these variables to be adjusted (find the enum at the top of the code sheet):-
    Enum Nws                        ' Worksheet navigation
        NwsCourse = 1               ' 1 = column A
        NwsVenue                    ' with no value assigned, increment by 1
        NwsHalf
        NwsFull
        NwsInput = 7                ' 7 = column G
        ' Rows:-
        NwsFirstDataRow = 3
        NwsCourses = 3              ' Cell(NwsCourses, NwsInput)
        NwsVenues
        NwsDuration
        NwsResult = 7
    End Enum

Open in new window

You can assign any value to any of the above. Their use isn't interrelated in any way.

In addition to the code you will find in Module1 there is this code in the code sheet of the worksheet on which the drop-downs are (moving them to a sheet other than the price list would require minor modification in the code).
Private Sub Worksheet_Change(ByVal Target As Range)

    With Target
        If .Address = Cells(NwsCourses, NwsInput).Address _
        Or .Address = Cells(NwsVenues, NwsInput).Address _
        Or .Address = Cells(NwsDuration, NwsInput).Address _
        Or .Address = Cells(NwsResult, NwsInput).Address Then WriteCourseFee
    End With
End Sub

Open in new window

I draw your attention to the fact that the Result cell is included in the above list. Changing it triggers its recalculation. This would prevent someone from falsifying a quote.

On my laptop the function runs fast enough for my taste. If - because of taste, technology or a substantially larger list of courses - you find it too slow note that it could be sped up substantially by adding a helper column to the worksheet (in fact, anywhere in the workbook).
EXX-131130-3-Criteria-Lookup.xlsm
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39686409
You can simply use the same formula I gave you.

Sub getresult()
Dim result As Integer
result = Evaluate("=SUMPRODUCT(PricesBasicTable[[Half day]:[Full day]]*" & _
                  "(PricesBasicTable[Course]=G3)*" & _
                  "(PricesBasicTable[Venue]=G4) *" & _
                  "(PricesBasicTable[[#Headers],[Half day]:[Full day]]=G5)) ")
End Sub
0
 

Author Comment

by:Tocogroup
ID: 39687774
Hi ssaqibh. Your formula works well,. However, what if the criteria values in G3 to G5 are in user form controls (text boxes) or variables ? How do I code it then ? I couldn't get it to work.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Tocogroup
ID: 39687781
Hi Faustalus. Thank you for your very comprehensive solution. I can use and adapt this in another application I'm building as it is extremely robust and easy to amend in the way you have structured it.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39687828
Something like

Evaluate("=SUMPRODUCT(PricesBasicTable[[Half day]:[Full day]]*" & _
                  "(PricesBasicTable[Course]=" & G3 & ")*" & _
                  "(PricesBasicTable[Venue]=" & G4 & ") *" & _
                  "(PricesBasicTable[[#Headers],[Half day]:[Full day]]=" & G5 & ")) ")

You can now replace the g3, g4, g5 with numbers or variables.
0
 

Author Comment

by:Tocogroup
ID: 39688346
Hmm...still can't get it to work. I replaced the cell addresses with variables which I loaded to test them. Here's my adapted code...

Private Sub cbGo_Click()

   Dim result As Integer
   Dim strCourse As String
   Dim strVenue As String
   Dim strDuration As String
   
   strCourse = "EXCEL"
   strVenue = "Inhouse"
   strDuration = "Full day"
   
   result = Evaluate("=SUMPRODUCT(PricesBasicTable[[Half day]:[Full day]]*" & _
                  "(PricesBasicTable[Course]=" & strCourse & ")*" & _
                  "(PricesBasicTable[Venue]=" & strVenue & ")*" & _
                  "(PricesBasicTable[[#Headers],[Half day]:[Full day]]=" & strDuration & ")) ")
   
   MsgBox ("result= " & result)

End Sub

I get a 'Run-time error 13 Type mismatch' on the Evaluate statement.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 250 total points
ID: 39688424
Sorry, it should have been

   result = Evaluate("=SUMPRODUCT(PricesBasicTable[[Half day]:[Full day]]*" & _
                  "(PricesBasicTable[Course]=""" & strCourse & """)*" & _
                  "(PricesBasicTable[Venue]=""" & strVenue & """)*" & _
                  "(PricesBasicTable[[#Headers],[Half day]:[Full day]]=""" & strDuration & """)) ")
0
 

Author Closing Comment

by:Tocogroup
ID: 39688536
Thank you both for your time and effort in providing  solutions to my query. I have adapted both for different applications.
Hope you don't mind me splitting the points as I've benefited from both explanations.
Regards
Toco
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now