Consulting two variables to determine precise value in a table

[McKavendish]

I'm new to this forum. Thank you for the opportunity to post my question.
I need to calculate a royalty owed. I have to multiply a per user rate times the number of users. The per user rate is based on 2 variables 1) whether there are a.<1000, b.<1000 <2000, or c. >2000 users and 2) what type of product is being used. How do I write a formula so Excel will look at 1) No. of users column and 2) type of product column and look up the rules to know what per user rate to use? I need to do the same thing for the flat % rate and then pay out the higher amount. I'm sure once I can do it for the per user rate I can calculate the rest.
I've left out the column with the actual # of users to reduce the size of this example.

Thank you!


Data
Type of
Customer No. Users Product Sales Amount Per User Rate
A Co. <1000 Perpetual $2,000
B Co. >2000 Maintenance $4,000
C Co. 1000< <2000 Perpetual $6,000


Rules Per user rates
<1000 1000< <2000 >2000
Perpetual $1.50 $1.25 $1.00
Maintenance $2.25 $2.00 $1.75

 

[gaz_chops]

By <1000 <2000 do you mean >1000 & <2000?

 

[vllgv]

Let me know if this gets you heading in the right direction:

Excel 2010
	A	B	C	D
1		0	1000	2000
2		2000	4000	6000
3		1.5	1.25	1
4		2.25	2	1.75
5
6	500	3875
7	999	5746.25
8	1000	7250
9	1001	7253.25
10	1500	8875
11	2000	11500
12	2001	11502.75
Sheet2 (2)

Cell Formulas
Range	Formula
B6	=INDEX($B$1:$D$4,2,MATCH(A6,$B$1:$D$1,1))+INDEX($B$1:$D$4,3,MATCH(A6,$B$1:$D$1,1))*A6+INDEX($B$1:$D$4,4,MATCH(A6,$B$1:$D$1,1))*A6

 

[McKavendish]

I apologize for not being more clear. The three categories of users are "less than 1,000" "greater than 1,000 and less than 2,000" and "greater than 2,000."
Thank you for your question and your interest in my problem.

 

[McKavendish]

I apologize for not being more clear. The three categories of users are "less than 1,000" "greater than 1,000 and less than 2,000" and "greater than 2,000."
Thank you for your question and your interest in my problem.

 

[McKavendish]

Vllgv thank you VERY much! I wasn't aware of INDEX MATCH. This is fantastic! I'm very grateful to you!

 

[vllgv]

Your welcome! Thank you for the feedback!