CODEWITHSUNDEEP
exceldateexcel-formula
user44350
user44350

Reputation: 15

EXCEL - Comparing a date to a column of date ranges and then, if within the range, comparing to a row cell

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.

enter image description here

Edit #3 Adding a rule that the formula should account for.

enter image description here

Thanks in advance!

Upvotes: 0

Views: 13090

Answers (2)

luckyguy73
luckyguy73

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

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

teylyn
teylyn

Reputation: 35990

Consider this screenshot:

enter image description here

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 row
  • these two arrays are multiplied one row at a time. If a TRUE is multiplied with a TRUE, the result is a 1. All other combinations will be 0.
  • That is the range that will be inspected for a matching 1. The position of the first 1 will be returned

enter image description here

Since 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

Related Questions