# Modifying 2 criteria based formula to 3

#### Nova1979

##### Board Regular
Hello All

I have recieved help with another post on 'creating a list based on 2 spercific criteria' from an extremely helpful and patient member.

=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))),"")

The above formula is run and finds a matching item from WO Shortages in WS Report. A list on a new sheet is created excluding matches found based on the 2 existing criteria (which is matching row data).

I am hoping that a third requirement can be added into this formula. When searched it checks WO Shortages column X (quantity required) against WS Report column O (quantity issued). If the numbers matched it is excluded.
If the quantity issued is less than quantity required then it is listed.

Here is a link to the one drive file.
I have not added the above formula to this. It is placed in Results sheet A2, copies across to AS2 then copied down

Thanks

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### 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)/(('WO Shortages'!\$X\$486:\$X\$3000>'WS Report'!\$O\$3:\$O\$3000)*(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
Fluff
In the formula you have put )+1)/(('WO Shortages'!\$X\$486:\$X\$3000>'WS Report'!\$O\$3:\$O\$3000)*( should it be )+1)/(('WO Shortages'!\$X\$2:\$X\$3000>'WS Report'!\$O\$2:\$O\$3000)*(
Thanks

#### Nova1979

##### Board Regular
Hi have run the formula you have suggested. Not sure what it has done, but not what I am hoping for.

WO Shortages X2 has a qty of 15 required.
WS Report O1737 (column C ref AABDH78636) indicates only 12 have been supplied.

NOTE: WO Shortages column Y is also indicating issued quantities

Based on the original formula, it is excluded from the created list due to the criteria set.
As the full qty has not been supplied, I am hoping to include it into the created list increasing the returned results from 40 rows after running the original formula.

When I run the formula you have given, this decreases the returned results from 40 rows to 11.

#### Fluff

##### MrExcel MVP, Moderator

I'm afraid I don't understand. If you want everything that has a shortage returned, then you will just get the entire shortage sheet again.

#### Nova1979

##### Board Regular
I am having to operate between 2 programs.
WO Shortages (items I require) will drop information into WS Report (items from supplier). Information will only move across if supplier has stock, even if supplier only has partial stock required. The problem I face is that once the full quantity of stock has been supplied, it stays in the WS Report.

The original idea was to create a list of everything that has NOT moved across

WO Shortages criteria 1 and 2 DO NOT match in WS Report then return a result (original formula)

WO Shortages criteria 1 and 2 DO match in WS Report then exclude from results (original formula)

I am hoping to be able to expand this to also identify items that have moved across where only partial quantity has been supplied. This means 1 or more has been issued to me.

WO Shortages criteria 1 and 2 DO match WS Report and 0 stock has been supplied then exclude from results. (This indicates the information has moved across but not yet been processed)

WO Shortages criteria 1 and 2 DO match WS Report and stock supplied is between 0 and the required amount then include this in the results (only a partial supply has been given which means supplier does not have enough)

WO Shortages criteria 1 and 2 DO match WS Report and the required stock matches the issued stock exactly then exclude from results. (This indicates the requirement has been met)

#### Fluff

##### MrExcel MVP, Moderator

I'm afraid that's beyond me. Hopefully somebody else will step in and help.

#### Nova1979

##### Board Regular
Hopeful concept. I am very happy with what you gave me in the other post.

#### Nova1979

##### Board Regular
To anyone that happens here, this is what I am after.

Base formula (explained above):
=IFERROR(INDEX('WO Shortages'!A\$2:A\$4000,AGGREGATE(15,6,(ROW('WO Shortages'!\$B\$2:\$B\$4000)-ROW('WO Shortages'!\$B\$2)+1)/((ISERROR(MATCH('WO Shortages'!\$B\$2:\$B\$4000,'WMS Report'!\$C\$2:\$C\$4000,0)))*(ISERROR(MATCH('WO Shortages'!\$E\$2:\$E\$4000,'WMS Report'!\$M\$2:\$M\$4000,0)))), ROWS(A\$2:A4))),"")

I am hoping to incorperate that if a matched result is found in the base formula then WO Shortages X2:X4000 checks against WS Report O2:O400.
If O2=0 or WO Shortages X2 and WS Report O2 match excatly then exclude from results
If O2 = less than X2 then include in results

Thanks

Replies
4
Views
164
Replies
28
Views
480
Replies
8
Views
798
Replies
11
Views
534
Replies
5
Views
216

1,127,572
Messages
5,625,584
Members
416,119
Latest member
JCLLE

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