Reversing an indexing formula

[Nova1979]

I have had help with the below formula (way out of my league). This searches and returns all results NOT found

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

Could someone help to reverse this to return all results found.

Thanks

 

[Chrisdontm]

I have something for you to try...
First of all, make a copy of your document and try this so you don't mess up your original document.

Put this formula where you had that one; ( In the correct row number)

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

I basically added the word "NOT" in two places, just before the two ISERROR formulas in the second half of the overall formula.
I hope it works for you...

 

[StephenCrump]

The key part of your equation is:

ISERROR(MATCH())*ISERROR(MATCH()) which means that matches fail on both column pairs.

If, by reverse, you mean matches on both column pairs succeed, you can simply change to:

ISNUMBER(MATCH())*ISNUMBER(MATCH()).

But if, by reverse, you mean matches on either column pair succeeds, you should change to:

NOT(ISERROR(MATCH())*ISERROR(MATCH()))

Incidentally, your formula will find the third non-match. (ROWS(A$2:A4) is just a convoluted way of generating the number 3). Presumably you have similar formulae in the two cells above to generate the first and second non-match?

 

[Nova1979]

Hi Stephen,
The formula was originally place in A2 and copied through to AE500. Its purpose was to return row data if both criteria did not match.

I have modified as you have indicated and it oooks like it works well

Thank you

 

[StephenCrump]

That's good to hear, thanks. But be careful with the ROWS reference, which determines the Nth result shown. Your starting formula in A2 should be of the form:

=IFERROR(INDEX('WO Shortages'!A$2:A$4000,AGGREGATE(15,6,YourFormulaForMatchingOrNotMatching, ROWS(A$2:A2))),"") so that when copied down, the 1st, 2nd, 3rd etc occurrence are shown.

If the starting formula in A2 has ROWS(A$2:A4), then when copied down you'll be showing the 3rd, 4th, 5th etc occurrence, and omitting the 1st and 2nd.