Extract a range based on a value

Hello everyone
In sample file I have 2 range (green and yellow)
In column A I have Power (kW) value(70 & 100)
In the blue range I want to have a formula that displays the corresponding range by entering the amount of power in cell F2
The correct answer for the value 70 is shown in the orange range.
Thank you for your help

In I2, copied across and down:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$15)/($A$2:$A$15=$F$2),ROWS(I$2:I2))),"")

ARRAY formula in I2 then copied across.

=IFERROR(INDEX(INDEX(A$2:A$15,MATCH($F$2,$A$2:$A$15,0)-1):INDEX(A$2:A$15,MATCH(2,1/($A$2:$A$15=$F$2),1)),ROWS(I$2:I2)),"")

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

You're right! It was an incorrect answer.

KVS's answer is rather clever... I like it! But if you prefer a non-array formula, you can use this:


=IFERROR(INDEX(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$15)-1/($A$2:$A$15=$F$2),1)):INDEX(A:A,AGGREGATE(14,6,ROW($A$2:$A$15)/($A$2:$A$15=$F$2),1)),ROWS(I$2:I2)),"")

in I2, copied across and down.