Index Match Unique Formula

[Lrodriguez]

Good Morning,

I need a Formula to find the best price based on the quantity chosen by the end user, which then would be chosen by the vendor and the imprint charges. the Imprint color will be a Drop down list, but the Quantity will be based on the End users desire.

so essentially the end user will say i want 250 shirts from vendor 1 with a 1 color imprint (drop down list) then it will put the price in as $1.50

Minimum Quantity	Vendor	1 Color Imprint	2 Color Imprint
12	Vendor 1	$2.25	$3.50
25	Vendor 1	$2.00	$2.95
50	Vendor 1	$1.85	$2.50
75	Vendor 1	$1.85	$2.25
100	Vendor 1	$1.60	$2.25
200	Vendor 1	$1.50	$2.00
12	Vendor 2	$4.00	$5.15
25	Vendor 2	$3.40	$4.25
50	Vendor 2	$3.05	$3.75
75	Vendor 2	$3.05	$3.40
100	Vendor 2	$2.85	$3.40
200	Vendor 2	$2.85	$3.40
12	Vendor 3	$3.29	$4.95
25	Vendor 3	$2.75	$3.95
50	Vendor 3	$2.40	$2.75
75	Vendor 3	$2.00	$2.40
100	Vendor 3	$1.90	$2.40
200	Vendor 3	$1.75	$2.25
12	Vendor 4	$1.40	$1.75
25	Vendor 4	$1.50	$1.50
50	Vendor 4	$1.25	$1.50
75	Vendor 4	$1.25	$1.50
100	Vendor 4	$1.25	$1.35
200	Vendor 4	$1.10	$1.35

 

[maabadi]

If your table Range is A2:D25, then your first Criteria at Cell G2 ("Vendor 1" or ...) & your Second Criteria at Cell H2 ("1 Color Imprint" or "2 Color Imprint")
Then your Header for "1 Color Imprint" is C1
you can use this formula:

=VLOOKUP(250, IF(F2="Vendor 1",$A$2:$D$7,IF(F2="Vendor 2",$A$8:$D$13,IF(F2="Vendor 3",$A$14:$D$19,$A$20:$D$25))),IF(G2=C1,3,4),TRUE)

 

[Fluff]

Another option

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H
1	Minimum Quantity	Vendor	1 Color Imprint	2 Color Imprint				Qty
2	12	Vendor 1	2.25	3.5		Vendor	Vendor 1	250
3	25	Vendor 1	2	2.95		Colours	1 Color Imprint	1.5
4	50	Vendor 1	1.85	2.5
5	75	Vendor 1	1.85	2.25
6	100	Vendor 1	1.6	2.25
7	200	Vendor 1	1.5	2
8	12	Vendor 2	4	5.15
9	25	Vendor 2	3.4	4.25
10	50	Vendor 2	3.05	3.75
11	75	Vendor 2	3.05	3.4
12	100	Vendor 2	2.85	3.4
13	200	Vendor 2	2.85	3.4
14	12	Vendor 3	3.29	4.95
15	25	Vendor 3	2.75	3.95
16	50	Vendor 3	2.4	2.75
17	75	Vendor 3	2	2.4
18	100	Vendor 3	1.9	2.4
19	200	Vendor 3	1.75	2.25
20	12	Vendor 4	1.4	1.75
21	25	Vendor 4	1.5	1.5
22	50	Vendor 4	1.25	1.5
23	75	Vendor 4	1.25	1.5
24	100	Vendor 4	1.25	1.35
25	200	Vendor 4	1.1	1.35
Master

Cell Formulas
Range	Formula
H3	H3	=INDEX(FILTER(SORT(FILTER(A2:D25,(B2:B25=G2)*(A2:A25<=H2)),1,-1),COUNTIF(G3,A1:D1)),1)

 

[Lrodriguez]

On the second option, when i enter that formula it gives me a #CALC! error.

 

[Lrodriguez]

Please Ignore** it would help if i spelled it correctly. SMH

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Eric W]

A little late, but another option:

=AGGREGATE(15,6,C2:D25/(B2:B25=G2)/(A2:A25<=H2)/(C1:D1=G3),1)