Match / Index multiple criteria return multiple results

[Marty]

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

 

[FSt1]

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 bar>data>filter>Advanced 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

 

[Max]

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,SMALL($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.