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

Match / Index multiple criteria return multiple results
 

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

Match / Index multiple criteria return multiple results
 

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