VBA Lookup on multiple criteria

[osx99]

I have the following array formula entered into a cell in excel which works fine to lookup on multiple criteria and return the relevent value

{ =INDEX('ELR Lookup'!d$2:d$1477,MATCH(H100,IF(O100>='ELR Lookup'!b$2:b$1477,IF(P100<='ELR Lookup'!c$2:c$1477,IF(H100='ELR Lookup'!A$2:A$1477,'ELR Lookup'!A$2:A$1477),0)))) }

However, the calc/recalc time is far too slow for the number of rows I need to use this

Instead I need to replicate with VBA. Can anyone give me any pointers on how to achieve this with reletively short code?

An example of the data I need to look up is

Main Sheet
Cell a100 = BJW3
Cell b100 = 17
cell c100 = 18

Cell D should return "Secondary" - (ie colA =BJW3, colC <=17, colC >=18)

ELR Lookup Sheet:
col A	col B	col C	col D
BJW1	0.0013	0.142	Secondary
BJW2	5.091	6.1736	Secondary
BJW3	12.109	16.1254	Primary
BJW3	16.1254	18.0132	Secondary
BJW3	18.0132	19	Primary
BJW3	19	23.0733	Primary
BKE	36.0369	37.1364	Primary

Many thanks
Os

 

[bluedragon2]

Have a look at the DLOOKUP formula

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[osx99]

I've found a neat alterantive elsewhere - here it is incase anyone else needs for future

Sub Findmultiplecriteria()
Dim vOurResult
Dim rFoundIt As Range
Dim iLoop As Integer

    With Sheet1.Range("Data")
        'Set variable to start search from
         Set rFoundIt = .Cells(1, 1)
            'Loop no more times that the name occurs
             For iLoop = 1 To WorksheetFunction.CountIf _
                                (Sheet1.Range("Data"), "multiplecriteria")

                'ReSet variable to found occurence of name. Next loop search _
                will start AFTER this Set cell.
                Set rFoundIt = .Find(What:="multiplecriteria", After:=rFoundIt, _
                LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False)

               'Check if it's the right one.
                If rFoundIt.Offset(0, -1).Value = 35 Then
                    vOurResult = rFoundIt.Offset(0, 3)
                    Exit For 'Leave loop
                End If

            Next iLoop
    End With 'Sheet1.Range("Data")

    If Not IsEmpty(vOurResult) Then 'Variable holds a value
        MsgBox vOurResult
    End If
End Sub