Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 116
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,942
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Index & Match functions - multiple criteria and multiple results [email protected] Excel Worksheet Functions 4 May 2nd 07 03:13 AM
Obtaining Multiple Results Using Index/Match Functions Archie999 Excel Worksheet Functions 1 March 3rd 07 08:57 AM
Obtaining Multiple Results Using Index/Match Functions Teethless mama Excel Worksheet Functions 0 March 3rd 07 04:16 AM
Obtaining Multiple Results Using Index/Match Functions Archie999 Excel Worksheet Functions 1 March 3rd 07 04:14 AM
Index/Match Multiple Criteria EstherJ Excel Discussion (Misc queries) 2 March 31st 06 12:54 PM


All times are GMT +1. The time now is 12:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"