Reputation: 15
I am having trouble getting a working formula for this without using VBA (I can use VBA if needed but have no experience with it). So what I am attempting to do is take a given date, see if it is within a list if date ranges, and if it is within a certain date range, it should compare to a cell within the same row that it matches to the date range. If it doesn't, it should continue to search until it finds another date range match or exhausts the list and returns a value of false.
So far I have tried something along the lines of If(NumbertoMatch(VLOOKUP(AND(Date>Date1,Date<Date2),Table,NumbertoMatch,False),TRUE,FALSE)
Edit #2 Adding an image of what the cells would compare to.
Edit #3 Adding a rule that the formula should account for.
Thanks in advance!
Upvotes: 0
Views: 13090
Reputation: 1939
VBA solution assumes the data is in worksheet 1 with columns A, B, C, D, G & H populated. The code will grab the matching row # to column I. Copy this code to a module. If there is more than one row then it separates them with comma's as seen in this screenshot
use this code
Sub FindRow()
Dim ws As Worksheet: Set ws = ActiveWorkbook.Worksheets(1)
Dim collDateRng As Range: Set collDateRng = ws.Range("G2", ws.Range("G2").End(xlDown))
Dim rng As Range
Dim resultsRng As Range
Dim rowRng As Range
Dim result As String
Application.ScreenUpdating = False
With ws
collDateRng.Offset(0, 2).ClearContents
For Each rng In collDateRng
If .AutoFilterMode Then .AutoFilterMode = False
With .Cells(1, 1).CurrentRegion
.AutoFilter field:=1, Criteria1:=rng.Offset(0, 1).Value
.AutoFilter field:=3, Criteria1:="<" & rng.Value
.AutoFilter field:=4, Criteria1:=">" & rng.Value
With .Resize(.Rows.Count - 1, 4).Offset(1, 0)
If CBool(Application.Subtotal(103, .Cells)) Then
.Select
Set resultsRng = Selection.SpecialCells(xlCellTypeVisible)
Else
GoTo NothingFound
End If
End With
End With
For Each rowRng In resultsRng.Rows
If result <> "" Then
result = result & ", " & rowRng.Row
Else
result = rowRng.Row
End If
Next rowRng
rng.Offset(0, 2).Value = result
result = ""
NothingFound:
Next rng
If .AutoFilterMode Then .AutoFilterMode = False
.Cells(1, 1).Select
End With
Application.ScreenUpdating = True
End Sub
Upvotes: 0
Reputation: 35990
Consider this screenshot:
The formula in J2 is
=IF(SUMPRODUCT((G2>=$C$2:$C$15)*(G2<=$D$2:$D$15)),MATCH(1,(G2>=$C$2:$C$15)*(G2<=$D$2:$D$15),0)+1,"")
This is an array formula and must be confirmed with Ctrl + Shift + Enter.
The formula in I2 uses that row number and compares the identifier in that row with the value in H2. If there is no match, that comparison will throw an error, so the IfError catches that and turns it into a FALSE.
=IFERROR(INDEX(A:A,J2)=H2,FALSE)
Do not use whole columns in the array formula, as that will slow things down.
With formulas you will only ever find the FIRST occurrence of a match, so returning several row numbers for multiple matches will not be possible.
Edit: Explanation of the MATCH function.
MATCH(1,(G2>=$C$2:$C$15)*(G2<=$D$2:$D$15),0)
When entered as an array function, the following will happen:
(G2>=$C$2:$C$15)
will resolve to an array of True or False values, one for each cell(G2<=$D$2:$D$15)
will resolve to an array of True or False values, one for each rowSince the data starts in row 2 and I want the absolute row number, I have to add a 1 to the result from Match. Match returns a 12 because the date is matched to the 12th row of the data, which is row 13 in the spreadsheet.
You can see these steps play out with the Evaluate Formula tool on the Formulas ribbon.
Another edit:
This formula will only return TRUE if the date in column G falls in the time range AND the identifier in column H is the same as in column A:
=IFERROR(INDEX(A:A,IF(SUMPRODUCT((G2>=$C$2:$C$15)*(G2<=$D$2:$D$15)),MATCH(1,(G2>=$C$2:$C$15)*(G2<=$D$2:$D$15)*(H2=$A$2:$A$15),0)+1,""))=H2,FALSE)
Again, confirm with Ctrl + Shift + Enter. Also if there are multiple matches, only the first match will trigger the TRUE.
Or if you want just the row number
=MATCH(1,(G2>=$C$2:$C$16)*(G2<=$D$2:$D$16)*(H2=$A$2:$A$16),0)+1
Upvotes: 2