Array Formula in Data Validation

[Lrodriguez]

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​

 

[ExcelGzh]

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.