I have this data.

And i need to find de class of this range

102 | 01/05/2021 | 31/05/2021

The answer is 190 because is within the fourth range in the data.But i need to know how to make this kind of Vlookup to match with approximate dates and the code as a second condition. I tried to use Vlookup and Match but didnt get result for the inmediate superior value.

Hope you can help me.

I tried to concatenate Code with Beg. Date and use VlookUp and AND but haven't have success.

CodePudding user response：

If I understand you correctly ....

Column F1, G1, H1 is the criteria to be searched

Sub test()
Dim rgData As Range: Dim rg As Range
Dim rgBeg As Date: Dim rgEnd As Date
Dim dBeg As Date: Dim dEnd As Date
Dim rgCod As Integer

Set rgData = Range("A2", Range("A" & Rows.Count).End(xlUp))
rgCod = Range("F1").Value
rgBeg = Range("G1").Value
rgEnd = Range("H1").Value

    With rgData
        .Replace rgCod, True, xlWhole, , False, , False, False
        Set rg = .SpecialCells(xlConstants, xlLogical).Offset(0, 1)
        .Replace True, rgCod, xlWhole, , False, , False, False
    End With
        
    For Each cell In rg
        dBeg = cell.Value: dEnd = cell.Offset(0, 1).Value
        If rgBeg > dBeg And rgEnd < dEnd Then MsgBox cell.Offset(0, 2): Exit For
    Next
    
End Sub

But the message box will show the result 180, because 1-May-21 to 31-May-21 is within the range 10-Apr-20 to 31-Aug-21.

The code is just making a range which contains the code to be searched (in this case : 102), by replacing "102" into TRUE, set the cells which has TRUE value into rg variable, then put back "102" from TRUE.

Then it loop to each cell in the rg variable, making the dBeg as the beginning date and the dEnd as the end date. Then it compare if rgBeg is bigger then dBeg and rgEnd is less then dEnd... then the msgbox show the result which is the looped cell.offset(0,2) value.

CodePudding user response：

If you have Excel 365 or Excel 2021, you don't need VBA at all for this - just use the FILTER function.

Assuming your table top left is on A1, and your 'search criteria' are in F1:H1, then the following formula, entered in I1 will find the class for you:

=FILTER(D:D,(G1>=B:B)*(H1<=C:C)*(A:A=F1))