Returning all rows from a table that matches multiple criteria

siktir23

New Member
Joined
Feb 4, 2011
Messages
17
I know that what I'm trying to do is easier than I'm making it out to be, but I've gotten myself wrapped around the axle and can't get my brain to work properly.

I have as small data table with a list of "batches", what date those batches were built, and what workstations they were built on.

I have a second set of tables that resemble a calendar where I need to pull in the batches that were built on particular workstations on specific dates.
In the example below, the yellow shaded cells in E4:E6 need to pull in the batch numbers that were built on workstation MN(F1) and were built on 8/18/2014(E2).
I need the formulas to pull in ALL batches on the day they were built as is shown in cells E11:E13.

jg3xb6.png


I’ve used as many combinations of INDEX and MATCH that I can muster but only get it to pull the 1st instance of the workstation and date instead of ALL instances.
{=INDEX(Table_BuiltData,MATCH(1,(Table_BuiltData[workstation]=$A$1)*(Table_BuiltData[builtdate]=E$2),0),3)}

Any help would be greatly appreciated!
 
Last edited:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Try:
Change to fit your ranges. Formulas in E4 & E5 can just be copied down if you are careful how you set references.

These are array formulas and must be entered with
Excel Workbook
ABCDEFG
1WSBuildbatchWorkstationMS
2MN8/18/201482-38/18/20148/19/2014
3MN8/19/201482-4batchbatch
4MS8/18/201482-582-582-8
5MS8/18/201482-682-6
6MS8/18/201482-782-7
7MS8/19/201482-8
8TE8/18/201482-9
9TE8/19/201482-10
CTRL-SHIFT-ENTER.
 
Upvote 0
workstation
builtdatebatch
MN8/18/201482966-3
MN8/19/201483028T-2
MS8/18/201482975K-4
MS8/18/201482979K-1
MS8/18/201483013-1
MS8/19/201483028T-1
TE8/18/201482975K-5
TE8/19/201483076-1
TE8/19/201483091-1
TE8/20/201483091-4
TE8/20/201483091-5
TN8/18/201482975K-1
TS8/18/201482979K-5
TS8/20/201483188K

<tbody>
</tbody>

Workstation:
MNWorkstation:TS
8/18/20148/19/20148/18/20148/19/20148/20/2014
BatchBatchBatchBatchBatch
82966-383028T-282979K-583188K
Workstation:MSWorkstation:TE
8/18/20148/19/20148/18/20148/19/20148/20/2014
BatchBatchBatchBatchBatch
82975K-483028T-182975K-583076-183091-4
82979K-183091-183091-5
83013-1
Workstation:TN
8/18/20148/19/2014
BatchBatch
82975K-1

<tbody>
</tbody>

I've made an effort to paste in my tables to make it easier for everyone to tinker with. Not sure what the best way to do this is...
 
Upvote 0
Did you try my formula in post 2 (seems to work)?
Below is an example with table nomenclature.

These are array formulas and must be entered with
Excel Workbook
ABC
1workstationbuiltdatebatch
2MN8/18/201482966-3
3MN8/19/201483028T-2
4MS8/18/201482975K-4
5MS8/18/201482979K-1
6MS8/18/201483013-1
7MS8/19/201483028T-1
8TE8/18/201482975K-5
9TE8/19/201483076-1
10TE8/19/201483091-1
11TE8/20/201483091-4
12TE8/20/201483091-5
13TN8/18/201482975K-1
14TS8/18/201482979K-5
15TS8/20/201483188K
16
17Workstation:MN
188/18/20148/19/2014
19BatchBatch
2082966-383028T-2
21
22
23
24Workstation:MS
258/18/20148/19/2014
26BatchBatch
2782975K-483028T-1
2882979K-1
2983013-1
30 
CTRL-SHIFT-ENTER.
 
Upvote 0
Your welcome. Glad you got it to work, thanks for the feedback.
 
Upvote 0
Your welcome. Glad you got it to work, thanks for the feedback.

Yeah, that's going to work just fine. BTW, I posted my tables before I noticed you answered the first time. Didn't want you to think I was asking for more help and ignoring your suggestion.

Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top