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

Posted on 2013-11-29
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
Question by:Tocogroup
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
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))

Author Comment

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

Assisted Solution

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
        NwsInput = 7                ' 7 = column G
        ' Rows:-
        NwsFirstDataRow = 3
        NwsCourses = 3              ' Cell(NwsCourses, NwsInput)
        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).
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Author Comment

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.

Author Comment

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.
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.

Author Comment

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.
LVL 43

Accepted Solution

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 & """)) ")

Author Closing Comment

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.

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

635 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