Question: How do I filter values between 0.5$ and 1.5$ from two columns using excel array formula?

Answer:

filter-values-from-two-columns

Array formula in A12:

=IFERROR(INDEX(List1, SMALL(IF((Price1<1,5)*(Price1>0,5), ROW(Price1)-MIN(ROW(Price1))+1), ROWS($A$12:A12))), INDEX(List2, SMALL(IF((Price2<1,5)*(Price2>0,5), ROW(Price2)-MIN(ROW(Price2))+1), ROWS($A$12:A12)-SUM(IF((Price1<1,5)*(Price1>0,5), 1, 0))))) + CTRL + SHIFT + ENTER

copied down as far as necessary

Array formula in B12:

=IFERROR(INDEX(Price1;SMALL(IF((Price1<1,5)*(Price1>0,5);ROW(Price1)-MIN(ROW(Price1))+1);ROWS($B$12:B12)));INDEX(Price2;SMALL(IF((Price2<1,5)*(Price2>0,5);ROW(Price2)-MIN(ROW(Price2))+1);ROWS($B$12:B12)-SUM(IF((Price1<1,5)*(Price1>0,5);1;0))))) + CTRL + SHIFT + ENTER

Named ranges
List1 (A2:A6)
List2 (C2:C6)
Price1 (B2:B6)
Price2 (D2:D6)
What is named ranges?

How to implement array formula to your workbook
Change named ranges. If your list starts at, for example, F2. Change $B$12:B12  in the above formula to F2:$F$2.

Download excel example file.
extract-values-from-two-columns.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array,k) Returns the k-th smallest row number in this data set.

ROWS(array) returns the number of rows in a reference or an array

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise