Match / Index multiple criteria return multiple results

Help I'm stuck!

Sample Data

Sheet1
A B C
Stock# Sales Person Type
90012 Bill New
91048 Sam Used
90456 Bill Used
91234 Frank New
91114 Bill New
91486 Bill New

Sheet 2
A B C
Bill New (Drop down boxes to select
search criteria)

Results I'm wanting if "BILL" in b1 and "New" in C1:
90012 Bill New
91114 Bill New
91486 Bill New

Results I'm wanting if "BILL" in b1 and "Used" in C1:
90456 Bill Used

Results I'm wanting if "BILL" in b1 and "" in C1:
90012 Bill New
90456 Bill Used
91114 Bill New
91486 Bill New

Stock numbers are unique if that helps

hi
formulas return values to the cell in which they reside. so i doubt that a
formula solution would provide what you want.
i would suggest that you use filter on sheet 1.
on the menu bardatafilterAdvanced filter
from the file drop down select Bill in B and New in C.
for both new and used, just select bill in B.

Regards
FSt1

"Marty" wrote:

Help I'm stuck!

Sample Data

Sheet1
A B C
Stock# Sales Person Type
90012 Bill New
91048 Sam Used
90456 Bill Used
91234 Frank New
91114 Bill New
91486 Bill New

Sheet 2
A B C
Bill New (Drop down boxes to select
search criteria)

Results I'm wanting if "BILL" in b1 and "New" in C1:
90012 Bill New
91114 Bill New
91486 Bill New

Results I'm wanting if "BILL" in b1 and "Used" in C1:
90456 Bill Used

Results I'm wanting if "BILL" in b1 and "" in C1:
90012 Bill New
90456 Bill Used
91114 Bill New
91486 Bill New

Stock numbers are unique if that helps

In Sheet2,
Put in D1: =IF(AND(Sheet1!B2=B$1,Sheet1!C2=C$1),ROW(),"")
Put in E1:
=IF(ROWS($1:1)COUNT($D:$D),"",INDEX(Sheet1!A:A,SM ALL($D:$D,ROWS($1:1))+1))
Copy E1 to G1. Select D1:G1, fill down to cover the max expected extent of
source data in Sheet1. Min/hide away col D. The desired results dependent on
the inputs in B1:C1 will be extracted into cols E to G, all neatly packed at
the top. Inspiring? hit the YES below.
--
Max
Singapore
---
"Marty" wrote:
Sample Data

Sheet1
A B C
Stock# Sales Person Type
90012 Bill New
91048 Sam Used
90456 Bill Used
91234 Frank New
91114 Bill New
91486 Bill New

Sheet 2
A B C
Bill New (Drop down boxes to select
search criteria)

Results I'm wanting if "BILL" in b1 and "New" in C1:
90012 Bill New
91114 Bill New
91486 Bill New

Results I'm wanting if "BILL" in b1 and "Used" in C1:
90456 Bill Used

Results I'm wanting if "BILL" in b1 and "" in C1:
90012 Bill New
90456 Bill Used
91114 Bill New
91486 Bill New

Stock numbers are unique if that helps