Solved

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

Posted on 2013-11-29
10
463 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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
 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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 create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

770 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