# Need to create multi-criteria lookup without helper column

#### gmomo

##### New Member
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 -​

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"

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

<tbody>
</tbody>

Sheet name "Stock"

<tbody>
</tbody>

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### gmomo

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

Replies
7
Views
858
Replies
5
Views
464
Replies
3
Views
142
Replies
2
Views
52
Replies
1
Views
188

1,127,990
Messages
5,628,001
Members
416,286
Latest member
ko15

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