# creating a list based on 2 exact criteria

#### Nova1979

##### Board Regular
Hello All,

Ideally I would like a formula to do this if it is at all possible

I am needing to run a 2 criteria search from WO Shortages (column B and E) against WS Report (column C and M) and only create a list in Results that does not have a match
The match MUST equal both criteria. If only one of the criteria is matched, then it should add to the list in Results
In the supplied sample images, I have highlighted 3 rows, these are matched on both requirements of the criteria from WO Shortages in WS Report based . I have placed the highlighted rows in the Results away from the list to indicate that it is that they should not be part of the main list that is returned. (These rows should not appear in the actual Results sheet)

If I have not explained enough, please let me know
Unfortunatley I am unable to utilse XL2BB hence the screen shots, so I appologise for this

#### Attachments

• Results.JPG
48.6 KB · Views: 11
• WO Shortages.JPG
65.2 KB · Views: 11
• WS Report.JPG
93.1 KB · Views: 6

#### Fluff

##### MrExcel MVP, Moderator
=IFERROR(INDEX('WO Shortages'!A\$2:A\$1000,AGGREGATE(15,6,(ROW('WO Shortages'!\$B\$2:\$B\$1000)-ROW('WO Shortages'!\$B\$2)+1)/(('WO Shortages'!\$B\$2:\$B\$1000<>'WS Report'!\$C\$2:\$C\$1000)*('WO Shortages'!\$E\$2:\$E\$1000<>'WS Report'!\$M\$2:\$M\$1000)), ROWS(A\$2:A2))),"")

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Nova1979

##### Board Regular
Sorry. All that seems to have done is copy WO Shortages line for line to results.

#### Fluff

##### MrExcel MVP, Moderator
=IFERROR(INDEX('WO Shortages'!A\$2:A\$3000,AGGREGATE(15,6,(ROW('WO Shortages'!\$B\$2:\$B\$3000)-ROW('WO Shortages'!\$B\$2)+1)/((ISERROR(MATCH('WO Shortages'!\$B\$2:\$B\$3000,'WS Report'!\$C\$2:\$C\$3000,0)))*(ISERROR(MATCH('WO Shortages'!\$E\$2:\$E\$3000,'WS Report'!\$M\$2:\$M\$3000,0)))), ROWS(A\$2:A2))),"")

#### Nova1979

##### Board Regular
Hello Fluff,

I have run the formula on a trial and it looks to be working. I will do a complete run early next week and let you know.

Thanks again

#### Fluff

##### MrExcel MVP, Moderator

You're welcome & thanks for the feedback

#### Nova1979

##### Board Regular
Hello Fluff,
The formula works extremely well.
I am wondering if it is possible to add a third component regarding quantities or is that getting too complicated?

The above formula is run and finds a matching item from WO Shortages in WS Report. This would be excluded from the list being creadted based on the 2 existing criteria.
A third requirement is then searched which checks WO Shortages column X (quantity required) against WS Report column O (quantity issued). If the numbers matched it is excluded.
If the issue quantity is less than required then it is listed

If this is not possible, or is overly complex, I am extremely happy with the supptort and help you have already provided.

Thanks

#### Fluff

##### MrExcel MVP, Moderator

I think it's best if you start a new thread for this. Thanks.

#### Nova1979

##### Board Regular
As it is along the same line for help. Won’t it get locked/cancelled?

Nope

Replies
8
Views
135
Replies
11
Views
300
Replies
1
Views
66
Replies
4
Views
313
Replies
1
Views
52

1,127,422
Messages
5,624,699
Members
416,042
Latest member
Oden

### 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.

### Which adblocker are you using?

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

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