• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

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

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
  • 5
  • 4
2 Solutions
Saqib Husain, SyedEngineerCommented:
=SUMPRODUCT(PricesBasicTable[[Half day]:[Full day]]*(PricesBasicTable[Course]=G3)*(PricesBasicTable[Venue]=G4)*(PricesBasicTable[[#Headers],[Half day]:[Full day]]=G5))
TocogroupAuthor Commented:
Wow ! That's impressive.
How do I convert that function into my VBA application ?
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).
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Saqib Husain, SyedEngineerCommented:
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
TocogroupAuthor Commented:
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.
TocogroupAuthor Commented:
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.
Saqib Husain, SyedEngineerCommented:
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.
TocogroupAuthor Commented:
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.
Saqib Husain, SyedEngineerCommented:
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 & """)) ")
TocogroupAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now