CODEWITHSUNDEEP
excelexcel-formula
Jonjon Candare
Jonjon Candare

Reputation: 41

Excel Search by Date Range and Name

I have this formula

=INDEX(C1:C8,LARGE(IF((A1:A8>=H2)*(A1:A8<=H3)*(B1:B8=H4),ROW(C1:C8),""),1))

it will get the last section from range and name

enter image description here

but it gets laggy when i created a summary of section where i use this code

enter image description here

can anyone suggest a formula where it wont get lag

Upvotes: 2

Views: 191

Answers (2)

James
James

Reputation: 123

Try either of the following code (using excel vba as a "sub" or within the worksheet as a "function"):

Function Code:

Function Test02(name01 As String, MaxDate As Range, MinDate As Range, Rng01 As Range)

'Rng01 Column 1 = "Date"
'Rng01 Column 2 = "jonjon"
'Rng01 Column 3 = "mtce"

Dim Arr01 As Variant ' Array of data
Dim i01 As Long 'Counter
Dim Temp01 As String 'saves the current "mtce" until a larger date is found
Dim TempDate

TempDate = 0
Arr01 = Rng01

For i01 = 1 To UBound(Arr01, 1)
    If Arr01(i01, 1) < MaxDate And Arr01(i01, 1) > MinDate And Arr01(i01, 2) = name01 And TempDate < Arr01(i01, 1) Then
        Test02 = Arr01(i01, 3)
        TempDate = Arr01(i01, 1)
    End If
Next i01
End Sub

Instead of having a function, you could use a "Sub" instead and only run it when there is an update, and keep the outputs as static strings.) You might have to edit the following code to repeat over the whole data set, but this should be a good start. If you could give me a better snapshot of how the data is laid out, I might be able to write this code this for you (if this is what you want).

This would be the basis of the sub:

Sub Test01()

Dim Arr01 As Variant ' Array of data
Dim i01 As Long 'Counter
Dim Temp01 As String 'saves the current "mtce" until a larger date is found
Dim TempDate

'Rng01 Column 1 = "Date"
'Rng01 Column 2 = "jonjon"
'Rng01 Column 3 = "mtce"

MinDate = Range("H2")
MaxDate = Range("H3")
name01 = Range("H4")
Rng01 = Range("A1:C8")

TempDate = 0
Arr01 = Rng01

For i01 = 1 To UBound(Arr01, 1)
    If Arr01(i01, 1) < MaxDate And Arr01(i01, 1) > MinDate And Arr01(i01, 2) = name01 And TempDate < Arr01(i01, 1) Then
        Temp01 = Arr01(i01, 3)
        TempDate = Arr01(i01, 1)
    End If
Next i01

Range("H5") = Temp01
End Sub

Upvotes: 1

mark fitzpatrick
mark fitzpatrick

Reputation: 3310

There are many ways to achieve the result you want, but a lot depends on your version of Excel.

The LARGE and ROW might be causing the delay. You could replace LARGE with MAX.

=INDEX(C1:C8,MAX((A1:A8>=H2)*(A1:A8<=H3)*(B1:B8=H4)*ROW(C1:C8)))

If you have Excel 365, XMATCH might do better. It has an option to search bottom to top:

=INDEX(C1:C8,XMATCH(H4,IF((A1:A8>=H2)*(A1:A8<=H3),B1:B8),0,-1))

Filtering the lookup array with the IF will reduce the compare operations for XMATCH.

Upvotes: 1

Related Questions