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

Answer:

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

Related posts:

1. Extract largest values from two columns using array formula in excel
2. Merge three columns into one list in excel
3. Merge two columns into one list in excel
4. Filter unique distinct values from two ranges combined in excel 2007
5. Extract a unique distinct list from two columns using excel 2007 array formula
6. Filter common values from three columns in excel
7. Identify missing values in two columns using excel formula
8. Filter a column and create a new unique list sorted from A to Z using array formula in excel
9. Filter values existing in range 1 but not in range 2 using array formula in excel
10. Filter unique rows and sort by date using array formula in excel