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
  • 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).
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
first name and last initial in excel 11 29
vba to flip column 14 21
Excel 2007 VB Code for GCF 7 17
Countdown Timer Advanced 5 20
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

830 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