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