# Array Formula in Data Validation

#### Lrodriguez

##### New Member
I have a difficult project i cant seem to figure out. My goal is to make an order form for people to fill out based on a bid i put out for vendors to bid on clothing. They bid on the price of the shirt or short and then they also gave the prices for a 1 color imprint, 2 color imprint....

My first goal was to find the lowest price for a piece of clothing based on the vendor's bids using this formula: =INDEX(\$C\$1:\$F\$1,MATCH(G3,C3:F3,0)) that actually added the vendor's name in the cell for me.

Once i figured that out i realized some vendors were lower in their clothing item but might be higher in their imprint and vice versa. I still have not accomplished how to determine lowest bidder based on those two criteria. Base price + Imprint Cost

My current Goal is to make a Drop Down list for my end user to just pick the type of imprint.(1 Color, 2 Color, 1 Color Number, 2 Color Number...) and it would hopefully give them the correct price of the imprint job based on the quantity and then return lowest price based on the vendor. This is the formula i am using for this: =INDEX(\$T\$4:\$Y\$7,MATCH(C18,\$S\$4:\$S\$7,0),MATCH(E18,\$T\$3:\$Y\$3,1))

When i enter in that formula i get this Error "You may not use reference operators or array constants for Data Validation" I also tried to use the # when referencing my sheet/cell, and i also tried to define the name and that didnt work either.

 12-24 25-49 50-74 75-99 100-199 200-299 12 25 50 75 100 200 Vendor 1 \$2.25​ \$2.00​ \$1.85​ \$1.85​ \$1.60​ \$1.50​ Vendor 2 \$4.00​ \$3.40​ \$3.05​ \$3.05​ \$2.85​ \$2.85​ Vendor 3 \$3.29​ \$2.75​ \$2.40​ \$2.00​ \$1.90​ \$1.75​ Vendor 4 \$1.40​ \$1.50​ \$1.25​ \$1.25​ \$1.25​ \$1.10​

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### ExcelGzh

##### Board Regular
Lrodriguez

There must be something odd about the values in cells C18 and/or E18 and/or the ranges \$S\$4:\$S\$7 or \$T\$3:\$Y\$3, because if you try your formula with fixed values in all the cells it works fine.

Replies
6
Views
89
Replies
7
Views
507
Replies
7
Views
178
Replies
0
Views
61
Replies
5
Views
225

1,127,800
Messages
5,626,958
Members
416,211
Latest member
lanka123

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