Spreadsheet: 97652d1296766768-how-to-assign-a-value-w-inconsistent-data-sortingproblem-1-
Sheet: Sample Catalog

Widget Industries Price List - Effective: 12/9/2010
Widget Industries # | Category | Catalog Page # | Color Group | [NAME] | Pieces per Case | Case Price | Dozen Price | Piece Price
10210 |  | 8 | Colors | S - XL | 24 | 23.16 | 26.63 | 30.11
10210 |  |  |  | 2XL - 3XL | 24 | 25.54 | 29.37 | 33.2
10211 |  | 16 | Colors | LT | 12 | 18.53 | 21.31 | 24.09
10211 |  |  |  | XLT | 12 | 20.3 | 23.35 | 26.39 |  |  | What I'm really looking to end up with is a list on another worksheet that will show the appropriate pricing based upon the item number (col A), the color group (col D, problematic), and the size (col E, same problem). The problems arise b/c the color groups and size ranges Widget Industries use arent the same ones we use.
10211 |  |  |  | 2XT | 12 | 22.05 | 25.36 | 28.67
10212 |  | 16 | Colors | XS - XL | 12 | 14.04 | 16.15 | 18.25
10212 |  |  |  | 2XL - 3XL | 12 | 16.76 | 19.27 | 21.79 |  |  | [NAME]: |  | Is there a good way to insert, lookup, or assign the size values I want (S-XL,2XL,3XL,4XL,5XL,6XL)
10213 |  | 16 | Colors | XS - XL | 12 | 13.99 | 16.09 | 18.19 |  |  |  |  | based upon the "Size Range" (column E) values they use? (see worksheet "All Sizes & Size Ranges"for details)
10213 |  |  |  | 2XL - 3XL | 12 | 16.67 | 19.17 | 21.67 |  |  |  |  | Or, alternately, a good way to sort or manipulate the data into a more lookup-able form?
20212 |  | 110 | White | S - XL | 72 | 1.72 | 1.98 | 2.24
20212 |  |  |  | 2XL | 72 | 2.81 | 3.23 | 3.65
20212 |  |  |  | 3XL | 36 | 2.81 | 3.23 | 3.65
20212 |  |  |  | 4XL - 5XL | 12 | 2.81 | 3.23 | 3.65
20212 |  |  | Neutrals | S - XL | 72 | 1.72 | 1.98 | 2.24
20212 |  |  |  | 2XL | 72 | 2.81 | 3.23 | 3.65
20212 |  |  |  | 3XL | 36 | 2.81 | 3.23 | 3.65
20212 |  |  | Heathers | S - XL | 72 | 2.27 | 2.61 | 2.95
20212 |  |  |  | 2XL | 72 | 3.41 | 3.92 | 4.43
20212 |  |  |  | 3XL | 36 | 3.41 | 3.92 | 4.43
20212 |  |  |  | 4XL - 5XL | 12 | 3.41 | 3.92 | 4.43
20212 |  |  | Colors | S - XL | 72 | 2.49 | 2.86 | 3.24
20212 |  |  |  | 2XL | 72 | 3.81 | 4.38 | 4.95
20212 |  |  |  | 3XL | 36 | 3.81 | 4.38 | 4.95
20212 |  |  |  | 4XL - 5XL | 12 | 3.81 | 4.38 | 4.95
20212 |  |  | PFD | S - XL | 72 | 1.78 | 2.05 | 2.31
20212 |  |  |  | 2XL | 72 | 2.61 | 3 | 3.39
20212 |  |  |  | 3XL | 36 | 2.61 | 3 | 3.39
20215 |  | 18 | Colors | S - XL | 12 | 10.95 | 12.59 | 14.24
20215 |  |  |  | 2XL - 3XL | 12 | 13.17 | 15.15 | 17.12
20215 |  |  |  | 4XL | 6 | 14.45 | 16.62 | 18.79
20215 |  |  |  | 5XL | 3 | 15.58 | 17.92 | 20.25
20215 |  |  |  | 6XL | 3 | 16.97 | 19.52 | 22.06
20227 |  | 18 | Colors | LT | 12 | 13.97 | 16.07 | 18.16
20227 |  |  |  | XLT | 12 | 15.32 | 17.62 | 19.92
20227 |  |  |  | 2XT | 12 | 16.83 | 19.35 | 21.88
36549 |  | 36 | Colors | S - XL | 24 | 31.99 | 36.79 | 41.59
36549 |  |  |  | 2XL | 24 | 35.8 | 41.17 | 46.54
36549 |  |  |  | 3XL | 24 | 36.81 | 42.33 | 47.85
36548 |  | 74 | Colors | S - XL | 36 | 9.72 | 11.18 | 12.64
36548 |  |  |  | 2XL | 24 | 11.24 | 12.93 | 14.61
36548 |  |  |  | 3XL | 12 | 12.81 | 14.73 | 16.65
36548 |  |  |  | 4XL | 12 | 14.28 | 16.42 | 18.56
36548 |  |  |  | 5XL | 12 | 15.99 | 18.39 | 20.79
99292 |  | 39 | Colors | S - XL | 24 | 29.99 | 34.49 | 38.99
99292 |  |  |  | 2XL | 24 | 33.5 | 38.53 | 43.55
99292 |  |  |  | 3XL | 24 | 35.5 | 40.83 | 46.15
87854 |  | 39 | Colors | S - XL | 24 | 29.99 | 34.49 | 38.99
87854 |  |  |  | 2XL | 24 | 33.29 | 38.28 | 43.28
87595 |  | 39 | Colors | S - XL | 24 | 29.99 | 34.49 | 38.99
87595 |  |  |  | 2XL | 24 | 32.99 | 37.94 | 42.89
87595 |  |  |  | 3XL | 24 | 34.99 | 40.24 | 45.49
66222 |  | 39 | Colors | S - XL | 24 | 29.99 | 34.49 | 38.99
66222 |  |  |  | 2XL | 24 | 33.39 | 38.4 | 43.41
66223 |  | 40 | Colors | S - XL | 24 | 27.85 | 32.03 | 36.21
66223 |  |  |  | 2XL | 24 | 30.91 | 35.55 | 40.18
66223 |  |  |  | 3XL | 24 | 33.07 | 38.03 | 42.99