Vlookup Multiple Criteria

[bobkap]

I need to determine the pricing based on the company, number of colors and quantity.

I thought I knew how to figure this out with a vlookup formula but I just seem to have forgotten.

If possible, can someone please help me determine that formula to determine the price based on the 3 criteria?

Also, getting the version and code to use in VBA would be great too.

Sample of data:

Company	# colors	13 qty	25	37	48	73
DMG	1​	$ 4.10	$ 2.85	$ 2.85	$ 1.90	$ 1.55
DMG	2​	$ 5.30	$ 3.75	$ 2.85	$ 2.65	$ 2.10
DMG	3​	$ 7.05	$ 4.60	$ 4.60	$ 3.45	$ 2.65
DMG	4​	$ 8.05	$ 5.95	$ 5.95	$ 4.10	$ 3.20
FE	1​	$ 2.25	$ 2.25	$ 1.60	$ 1.60	$ 1.40
FE	2​	$ 2.35	$ 2.35	$ 1.80	$ 1.80	$ 1.55
FE	3​	$ 2.55	$ 2.55	$ 2.00	$ 2.00	$ 1.70
FE	4​	$ 2.75	$ 2.75	$ 2.20	$ 2.20	$ 1.85
HTECH	1​	$ 2.88	$ 1.77	$ 1.77	$ 1.77	$ 1.14
HTECH	2​	$ 4.60	$ 2.25	$ 2.25	$ 2.25	$ 1.43
HTECH	3​	$ 6.66	$ 2.74	$ 2.74	$ 2.21	$ 1.71
HTECH	4​	$ 8.05	$ 3.11	$ 3.11	$ 2.50	$ 2.04

 

[SanjayGMusafir]

With Excel 365 prefer XLOOKUP

The format should be like - Fill as per the column address and Criteria(s)

=XLOOKUP(1,(CriteriaRange1=Criteria1)*(CriteriaRange2=Criteria2)*(CriteriaRange3=Criteria3),ReturnRange,"",0)