How to extracting a data between column range. Column range will vary.

[marvinmarapao]

I wanted to extract data that fall into my category using if function.  The data shall be extracted within multiple ranges and the range will vary. 

Need a solution for this.  Thank you.

[#2]

@marvinmarapao 

=IFERROR(IF(AND(INDIRECT($J$3)>=$J$2,INDIRECT($J$3)<=$K$2,INDIRECT($J$4)>$J$1,INDIRECT($J$4)<$K$1),B3,""),"")

Is this what you want to do? In the attached file you can dynamically change the criteria and the column range in the orange cells.

[#3]

@OliverScheurich thanks for the response.

I wanted to do sample as shown below.  Thanks for the help.

10   Sweet

11   Sour

25   Sour

15   Sweet

12   Sour

16   Sour

23   Sour

16   Sweet

Data required

1. Max value of range inside the range of sweet

  Range 1 = 25

  Range 2 =  23

Thanks for the help

[#4]

@marvinmarapao 

Sub sweet()

Dim i As Integer
Dim j As Integer
Dim row1 As Integer
Dim max As Integer

Range("E:E").Clear

row1 = Application.WorksheetFunction.Match("sweet", Range(Cells(1, 2), Cells(8, 2)), 0)
j = 1
For i = 1 To 15

If Cells(row1 + i, 2).Value = "sweet" Then
max = Application.WorksheetFunction.max(Range(Cells(row1 + 1, 1), Cells(row1 + i - 1, 1)))
row1 = row1 + i

Cells(j, 5).Value = max
j = j + 1
i = 0
Else
End If

Next i

End Sub

Maybe with these lines of code. I assume that there is at least one time sour within two times sweet and not more than 14 times sour within two times sweet. You can click the button in cell G2 in the attached file to start the macro.