I am trying to find a way to create a filtered list based on criteria. I
have the following formulas working in another spreadsheet...and I was
thinking maybe I could alter them to work.

Current Formulas:
Column A: Source Data (in this example Customer Name)

Column B:
=IF(ROW(A1)COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C: C,ROW(A1)),C:C,0)))

Column C: =IF(ISNUMBER(SEARCH($D$1,A1)),ROW(),"")

Cell D2: Filter Value

Basically the formula (from Max) filters the data in column B...giving me a
compact list of values that contain the value I type in cell D2.

Is it possible to add criteria to this formula? Or use another formula to
work? For example, I want a result list of values in Column C, from Column
A if E2=value in column B.

Column A: Source Data - Customer Name

Column B: Source Data - Variable Property

Column C: Similar Formula as above column B
=IF(ROW(A1)COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C: C,ROW(A1)),C:C,0)))

Column D: Similar Formula as above Column C
=IF(ISNUMBER(SEARCH($D$1,A1)),ROW(),"")

Cell E2: Filter Value

I don't know if that is clear or not...I would appreciate any help.

This set-up should deliver what you're after ..

Col A = Cust names, from row2 down, with possible duplicates
Col B = Corresponding values to be returned for the particular customer,
whose name will be entered in E2 (E2 will house the filter value)

Put in C2:
=IF(ROW(A1)COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW( A1))))

Put in D2:
=IF($E$2="","",IF(ISNUMBER(SEARCH($E$2,A1)),ROW(A1 ),""))
(Leave D1 blank)

Select C2:D2, copy down to cover the max expected extent of data in col A.
Col C will return the required results from col B for the cust name entered
in E2
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Josh O." wrote:
I am trying to find a way to create a filtered list based on criteria. I
have the following formulas working in another spreadsheet...and I was
thinking maybe I could alter them to work.

Current Formulas:
Column A: Source Data (in this example Customer Name)

Column B:
=IF(ROW(A1)COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C: C,ROW(A1)),C:C,0)))

Column C: =IF(ISNUMBER(SEARCH($D$1,A1)),ROW(),"")

Cell D2: Filter Value

Basically the formula (from Max) filters the data in column B...giving me a
compact list of values that contain the value I type in cell D2.

Is it possible to add criteria to this formula? Or use another formula to
work? For example, I want a result list of values in Column C, from Column
A if E2=value in column B.

Column A: Source Data - Customer Name

Column B: Source Data - Variable Property

Column C: Similar Formula as above column B
=IF(ROW(A1)COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C: C,ROW(A1)),C:C,0)))

Column D: Similar Formula as above Column C
=IF(ISNUMBER(SEARCH($D$1,A1)),ROW(),"")

Cell E2: Filter Value

I don't know if that is clear or not...I would appreciate any help.

Got it. Works perfect. Thanks Max.

Is it possible to use multiple criteria? Maybe by using another cell along
with E2 (as If E2 or E3)?

"Max" wrote:

This set-up should deliver what you're after ..

Col A = Cust names, from row2 down, with possible duplicates
Col B = Corresponding values to be returned for the particular customer,
whose name will be entered in E2 (E2 will house the filter value)

Put in C2:
=IF(ROW(A1)COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW( A1))))

Put in D2:
=IF($E$2="","",IF(ISNUMBER(SEARCH($E$2,A1)),ROW(A1 ),""))
(Leave D1 blank)

Select C2:D2, copy down to cover the max expected extent of data in col A.
Col C will return the required results from col B for the cust name entered
in E2
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Josh O." wrote:
I am trying to find a way to create a filtered list based on criteria. I
have the following formulas working in another spreadsheet...and I was
thinking maybe I could alter them to work.

Current Formulas:
Column A: Source Data (in this example Customer Name)

Column B:
=IF(ROW(A1)COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C: C,ROW(A1)),C:C,0)))

Column C: =IF(ISNUMBER(SEARCH($D$1,A1)),ROW(),"")

Cell D2: Filter Value

Basically the formula (from Max) filters the data in column B...giving me a
compact list of values that contain the value I type in cell D2.

Is it possible to add criteria to this formula? Or use another formula to
work? For example, I want a result list of values in Column C, from Column
A if E2=value in column B.

Column A: Source Data - Customer Name

Column B: Source Data - Variable Property

Column C: Similar Formula as above column B
=IF(ROW(A1)COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C: C,ROW(A1)),C:C,0)))

Column D: Similar Formula as above Column C
=IF(ISNUMBER(SEARCH($D$1,A1)),ROW(),"")

Cell E2: Filter Value

I don't know if that is clear or not...I would appreciate any help.

"Josh O." wrote:
Got it. Works perfect. Thanks Max.

Welcome.

Is it possible to use multiple criteria?
Maybe by using another cell along with E2 (as If E2 or E3)?

Yes, of course.

Assuming the range E2:E10 will house the input filter criteria

Just replace the formula in D2 with:
=IF(SUMPRODUCT((ISNUMBER(SEARCH($E$2:$E$10,A2))*($ E$2:$E$10<""))),ROW(),"")
(Leave D1 blank)

Then copy D2 down to cover the max expected extent of data in col A, as
before.
(Col C formulas unchanged). Col C will return all required results.

Adapt the input range to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I wish I understood how it works better...so I don't have to bug you with
questions.

Just one more and I am set:
How about searching for the filter values in 2 different columns?

"Max" wrote:

"Josh O." wrote:
Got it. Works perfect. Thanks Max.

Welcome.

Is it possible to use multiple criteria?
Maybe by using another cell along with E2 (as If E2 or E3)?

Yes, of course.

Assuming the range E2:E10 will house the input filter criteria

Just replace the formula in D2 with:
=IF(SUMPRODUCT((ISNUMBER(SEARCH($E$2:$E$10,A2))*($ E$2:$E$10<""))),ROW(),"")
(Leave D1 blank)

Then copy D2 down to cover the max expected extent of data in col A, as
before.
(Col C formulas unchanged). Col C will return all required results.

Adapt the input range to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

How about searching for the filter values in 2 different columns?

Assuming input filter values could be entered within E2:E10, and within
G2:G10, replace the formula in D2 with:

=IF(OR(SUMPRODUCT((ISNUMBER(SEARCH($G$2:$G$10,A2)) *($G$2:$G$10<""))),SUMPRODUCT((ISNUMBER(SEARCH($E $2:$E$10,A2))*($E$2:$E$10<"")))),ROW(),"")

Copy down as before ..

(No change to col C's formulas)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Josh O." wrote in message
...
I wish I understood how it works better...so I don't have to bug you with
questions.

Just one more and I am set:
How about searching for the filter values in 2 different columns?