Need to create multi-criteria lookup without helper column

[gmomo]

Hi folks, thanks for all the awesome tips thus far!

SUMMARY:
I have (1) purchase orders for crayons and (2) a list of several different companies with bagged crayons in varying sized quantities. I'd like to find orders that were made in sequential order that have matching SKU's that are 1-digit apart in incremental order (100,101 but not 101,100) without using a helper column

STEPS TAKEN:
1) The 'Orders' sheet (Column B&C) - contains purchase orders for crayon colors and the Qty. needed
2) All Sku's from the 'Stock' sheet that match the criteria (Color and Qty) are selected from various companies (ex. Shady, Grady, 1-size Betty) and placed (SMALL'd) on the 'Orders' sheet 'Matches area' (Column F to I) as helper columns for step 3.

Working formula in the "MATCHES" section (F4 to I19)
(This formula gets the appropriate SKU from the "stock" sheet and looks for: Matching color, and quantity)
(F4) =IFERROR(INDEX(Stock!$A$4:$A$25,SMALL(IF([@Color]=Stock!$B$4:$B$25,IF([@Qty]>=Stock!$D$4:$D$25,IF([@Qty]<=Stock!$E$4:$E$25,ROW(Stock!$A$4:$A$25)-ROW(Stock!$A$4)+1))),COLUMNS($A4:A4))),"")

3) On the 'Orders' sheet, I then created a '+1 SKU difference' formula (Columns K to N) to search through the Matches (F to I) and only show results for SKU's from the 'Stock' sheet that are 1-digit apart in incremental order.
Working formula for the SKU's that have a 1-digit incremental counterpart
(K4) =IFERROR(SMALL(IF(COUNTIF($F5:$I5,$F4:$I4+1),$F4:$I4),COLUMNS($A4:A4)),"")

PROBLEM:
I'm in need of doing the multi-criteria search for the current row and following row ('Orders' sheet row 4 and row 5) and showing SMALL'd results from left to right revealing all SKU's that have a +1 SKU in the next row within one single formula.

In case it's not clear, I need to somehow get this into one formula using this logic:

Find results from ('Orders' F4-I4)
=IFERROR(INDEX(Stock!$A$4:$A$25,SMALL(IF([@Color]=Stock!$B$4:$B$25,IF([@Qty]>=Stock!$D$4:$D$25,IF([@Qty]<=Stock!$E$4:$E$25,ROW(Stock!$A$4:$A$25)-ROW(Stock!$A$4)+1))),COLUMNS($A4:A4))),"")

and subtract the SKU number from the results of Sku's found as a result of this formula ('Orders' F5-I5)
=IFERROR(INDEX(Stock!$A$4:$A$25,SMALL(IF([@Color]=Stock!$B$4:$B$25,IF([@Qty]>=Stock!$D$4:$D$25,IF([@Qty]<=Stock!$E$4:$E$25,ROW(Stock!$A$4:$A$25)-ROW(Stock!$A$4)+1))),COLUMNS($A5:A5))),"")

and report all SKU's that have a 1-digit higher value in the following row like I did with this formula (K4-H4) =IFERROR(SMALL(IF(COUNTIF($F5:$I5,$F4:$I4+1),$F4:$I4),COLUMNS($A4:A4)),"")

- THE DATA SET -​

Filelink:
https://dl.dropboxusercontent.com/u/2447350/pics/Crayon Demo.xlsx

Note: Correct results are in the 'SKU DIFFERENCE = +1 (Column K through L) area on the 'Orders' sheet. It is currently searching through results from the Matches area. I will have thousands of these rows and want to eliminate the mess by creating a single formula.


Sheet name "Orders"

[TABLE="width: 881"]
<tbody>[TR]
[TD="colspan: 3"]PURCHASE ORDERS[/TD]
[TD][/TD]
[TD][/TD]
[TD]MATCHES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SKU
DIFFERENCE = +1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD](A) Order #[/TD]
[TD](B) Color[/TD]
[TD] (C) Qty[/TD]
[TD][/TD]
[TD][/TD]
[TD](F) SKU[/TD]
[TD](G) SKU2[/TD]
[TD](H) SKU3[/TD]
[TD](I) SKU4[/TD]
[TD][/TD]
[TD](K) +1 SKU DIFF[/TD]
[TD](L) +1 SKU DIFF2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Grey[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD][/TD]
[TD]101[/TD]
[TD]400[/TD]
[TD]601[/TD]
[TD][/TD]
[TD][/TD]
[TD]400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Grey[/TD]
[TD="align: right"]81[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]401[/TD]
[TD]601[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Black[/TD]
[TD="align: right"]90[/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[TD]701[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Blue[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[TD]801[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Blue[/TD]
[TD="align: right"]105[/TD]
[TD][/TD]
[TD][/TD]
[TD]201[/TD]
[TD]501[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Black[/TD]
[TD="align: right"]92[/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[TD]701[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Blue[/TD]
[TD="align: right"]116[/TD]
[TD][/TD]
[TD][/TD]
[TD]201[/TD]
[TD]501[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Black[/TD]
[TD="align: right"]120[/TD]
[TD][/TD]
[TD][/TD]
[TD]301[/TD]
[TD]703[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Black[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD][/TD]
[TD]702[/TD]
[TD]802[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Black[/TD]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[TD]701[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Grey[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]401[/TD]
[TD]601[/TD]
[TD]800[/TD]
[TD][/TD]
[TD]401[/TD]
[TD]601[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Grey[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD]402[/TD]
[TD]602[/TD]
[TD]800[/TD]
[TD][/TD]
[TD][/TD]
[TD]800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Blue[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]500[/TD]
[TD]801[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Blue[/TD]
[TD="align: right"]125[/TD]
[TD][/TD]
[TD][/TD]
[TD]501[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Black[/TD]
[TD="align: right"]51[/TD]
[TD][/TD]
[TD][/TD]
[TD]702[/TD]
[TD]802[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]702[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Black[/TD]
[TD="align: right"]121[/TD]
[TD][/TD]
[TD][/TD]
[TD]301[/TD]
[TD]703[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet name "Stock"

[TABLE="width: 505"]
<tbody>[TR]
[TD]STOCK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SKU#[/TD]
[TD]Color Group[/TD]
[TD]Package[/TD]
[TD]Qty Min[/TD]
[TD]Qty Max[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Grey[/TD]
[TD]Shady Grey Small[/TD]
[TD]80[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]Grey[/TD]
[TD]Shady Grey Large[/TD]
[TD]86[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]Blue[/TD]
[TD]Shady Blue Small[/TD]
[TD]80[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]201[/TD]
[TD]Blue[/TD]
[TD]Shady Blue Medium[/TD]
[TD]90[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]202[/TD]
[TD]Blue[/TD]
[TD]Shady Blue Large[/TD]
[TD]121[/TD]
[TD]115[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]Black[/TD]
[TD]Shady Black Small[/TD]
[TD]90[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]301[/TD]
[TD]Black[/TD]
[TD]Shady Black Large[/TD]
[TD]111[/TD]
[TD]125[/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD]Grey[/TD]
[TD]Grady Grey Large[/TD]
[TD]90[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]401[/TD]
[TD]Grey[/TD]
[TD]Grady Grey Med[/TD]
[TD]75[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]402[/TD]
[TD]Grey[/TD]
[TD]Grady Grey Small[/TD]
[TD]55[/TD]
[TD]74[/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD]Blue[/TD]
[TD]Grady Blue Ex Small[/TD]
[TD]15[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]501[/TD]
[TD]Blue[/TD]
[TD]Grady Blue Extra Large[/TD]
[TD]100[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]600[/TD]
[TD]Grey[/TD]
[TD]Grady Grey Special Large[/TD]
[TD]100[/TD]
[TD]119[/TD]
[/TR]
[TR]
[TD]601[/TD]
[TD]Grey[/TD]
[TD]Grady Grey Special Medium[/TD]
[TD]80[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]602[/TD]
[TD]Grey[/TD]
[TD]Grady Grey Special Small[/TD]
[TD]50[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD]700[/TD]
[TD]Black[/TD]
[TD]Grady Black Large[/TD]
[TD]100[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]Black[/TD]
[TD]Grady Black Medium[/TD]
[TD]61[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]702[/TD]
[TD]Black[/TD]
[TD]Grady Black Small[/TD]
[TD]50[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]703[/TD]
[TD]Black[/TD]
[TD]Grady Black Extra Large[/TD]
[TD]111[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]800[/TD]
[TD]Grey[/TD]
[TD]Betty 1-size[/TD]
[TD]10[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]801[/TD]
[TD]Blue[/TD]
[TD]Betty 1-size[/TD]
[TD]10[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]802[/TD]
[TD]Black[/TD]
[TD]Betty 1-size[/TD]
[TD]10[/TD]
[TD]80[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance!

 

[gmomo]

Due to my poor explanation, I'm assuming no one could figure out what I was looking for but, unfortunately, I don't know how to erase/edit the original.

I found a work around, but it involved searching for ROWs in sequential order instead of the SKU's which proved to be much more complex.