Excel VBA - array match with multiple criteria

[justhumm]

I have a large table, from which I want to pull out the maximum value from a particular column, only if the values in other rows match up to certain criteria. As an example, I want to pull out the maximum value in "COLF", but only for rows that match the following criteria:

"COLA" value begins with 'BS'...I don't care about the last characters.
"COLB" value is equal to 'DW'.
...so, for the table below, I want to get back 0.637 as a result:

COLA____COLB____COLC____COLD________COLF
----------------------------------------
BNA1____0.75____DC____LinStatic________8.105
BNA1____2.44____DC____LinStatic________8.105
BSA1____2.44____DC____LinStatic________3.581
BSA1____3.66____DC____LinStatic________3.581
BNA2____4.88____DC____LinStatic________3.581
BNA3____0.75____DW____LinStatic________1.307
BNA3____2.44____DW____LinStatic________1.307
BSA1____2.44____DW____LinStatic________0.637
BNA1____3.66____DW____LinStatic________0.637
BNA1____4.88____DW____LinStatic________0.637

In an attempt to do this, I threw together a function that's based on simpler code that someone gave to me, but I am extremely novice when it comes to VBA and am having problems:
------------------------------
Function Maxifandif(value1 As String, value2 As String)
'
Application.Volatile True ' Ensure that function will automatically recalculate
'
' Declare the Variables to be used
'
Dim lookrange1 As Range ' this is a single column array that the function looks for matches in
Dim lookrange2 As Range ' this is a single column array that the function looks for matches in
Dim returnrange As Range ' this is the single column array that the result is returned from
Dim lngCount As Long
Dim varArray()
'
' Assign Values to Required Variables
'
Set lookrange1 = Sheets("Test1").Range("C1:C10")
Set lookrange2 = Sheets("Test1").Range("A1:10")
Set returnrange = Sheets("Test1").Range("F1:F10")
'
' Assemble a temporary Array from rows that match the criteria (below)
'
ReDim varArray(1 To Application.WorksheetFunction.CountIf(lookrange1, value1))
lngCount = 1
For Each acell In returnrange
If lookrange1.EntireColumn.Cells(acell.Row) = value1 _
And lookrange2.EntireColumn.Cells(acell.Row) = value2 _
Then
varArray(lngCount) = acell
lngCount = lngCount + 1
End If
Next acell
Maxifandif = Application.WorksheetFunction.Max(varArray)
End Function
------------------------------
Due to the size of my actual table and the variation in the criteria that I have to look at, I am trying to make a user-defined function to do this. But I'm running into a wall...

If I set "value2 = BSA1", then the function works fine, but I can not figure out how to make the function work when "value2 = BS*" (where * would be some sort of wildcard character) and I can't figure out how to chop-off the trailing characters from the value of "acell" within the "For Each" loop.

Any input or suggestions would be appreciated...

 

[Denial]

I don't think you need to resort to VBA to achieve this.
An "array expression" should be able to be used.
Something along the lines of
=MAX((LEFT(A8:A19,2)="BS")*(C8:C19="DC")*F8:F19)
entered as an "array formula" [Ctrl-Shift-Enter].

(But it gets more complicated if you can have non-positive numbers in column F.)

 

[justhumm]

I was trying to avoid using the built-in functions, because I was working across multiple worksheets (plus I am working with both positive and negative values) and this was making the equations extremely long and difficult to review/update.

And FYI, I actually solved my original post by using the "like" operator instead of "=":

If lookrange1.EntireColumn.Cells(acell.Row) Like value1 _
And lookrange2.EntireColumn.Cells(acell.Row) Like value2 _

...that seems to have done the trick.