Match / Index multiple criteria return multiple results

  • Thread starter Thread starter Marty
  • Start date Start date
M

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
 
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
 
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.
 
Back
Top