Will INDEX,MATCH work for this?

[cintirich]

Hi all,

I am trying to come up with a formula that will look at data in 3 columns (SubscriberStatus [yes/no], MarketID [1-10], and Age [any integer 13-17]) and categorize the result into a "quota code". There are 30 quota codes. The first 10 (525 - 534) are based only on SubscriberStatus and MarketID. The rest (535 - 554) are based on all three variables.

Examples

• Quota Code 525 = Non-subscribers Market 1
• Quota Code 535 = 13-15 Year old Subscribers Market 1
• Quota Code 545 = 16-17 Year old Subscribers Market 1
• Quota Code 526 = Non-subscribers Market 2
• Quota Code 536 = 13-15 Year old Subscribers Market 2
• Quota Code 546 = 16-17 Year old Subscribers Market 2

Any ideas? Thanks!
Rich

Age	Subscriber	MarketID	Quota_Code
13	Yes	6	?
15	No	7	?
14	Yes	8	?
17	No	5	?
16	Yes	10	?
13	Yes	7	?

<tbody>
</tbody>

 

[CalcSux78]

Excel 2010
	A	B	C	D
1	Age	Subscriber	MarketID	Quota_Code
2	13	Yes	6	540
3	15	No	7	531
4	14	Yes	8	542
5	17	No	5	529
6	16	Yes	10	554
7	13	Yes	7	541
Sheet1

Cell Formulas
Range	Formula
D2	=IF(B2="No",524+C2,IF(A2<16,534+C2,544+C2))

 

[cintirich]

Excellent and elegant. Thanks!