Return target based on multiple factors

[KevyWevz]

Hello helpful Excel community,

I am not sure if it lack of knowledge or simple brain freeze but I am stuck on identifying the what and how to implement a function to return me a target based on multiple factors.

I have teams of fruit sales people who have different targets depending on their fruit specialization and length of time with the business.

So I have 3 teams of salespeople, with each team having 3 categories of sales people within team. Example:

Apple Sales Team:
- less than 6 months tenure = 2 boxes per day
- between 6 and 12 months tenure = 3 boxes per day
- greater than 12 months tenure = 4 boxes per day

Banana Sales Team:
- less than 6 months tenure = 3 boxes per day
- between 6 and 12 months tenure = 5 boxes per day
- greater than 12 months tenure = 6 boxes per day

Orange Sales People:
- less than 6 months tenure = 1 boxe(s) per day
- between 6 and 12 months tenure = 2 boxes per day
- greater than 12 months tenure = 3 boxes per day

As teach team has different targets depending on their specialization - is there a formula that can return the daily target (listed in another tab as per the above list) based on the sales person's team and tenure?

Any help at all much appreciated!

Kindest regards,
KW

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[KevyWevz]

Thank you - sample file updated.

[AliGW]

Add a band helper column to your lookup table:

Excel 2016 (Windows) 32 bit

	I	J	K	L
2	Team	Tenure	Band	Target
3	Apple	0-6 months	0	2
4	Apple	6-12 months	6	3
5	Apple	12+ months	12	4
6	Banana	0-6 months	0	3
7	Banana	6-12 months	6	5
8	Banana	12+ months	12	6
9	Orange	0-6 months	0	1
10	Orange	6-12 months	6	2
11	Orange	12+ months	12	3

Sheet: Targets

Then try this:

=INDEX($L$3:$L$11,MATCH(C2,IF($I$3:$I$11=B2,$K$3:$K$11),1))

You may need to confirm the formula entry with CTRL+SHIFT+ENTER before copying down.

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	SalesPerson	Team	Tenure (months)	Target
2	SalesPerson-A	Apple	24	4
3	SalesPerson-B	Apple	16	4
4	SalesPerson-C	Apple	2	2
5	SalesPerson-D	Banana	21	6
6	SalesPerson-E	Banana	14	6
7	SalesPerson-F	Banana	5	3
8	SalesPerson-G	Banana	9	5
9	SalesPerson-H	Orange	9	2
10	SalesPerson-I	Orange	11	2
11	SalesPerson-J	Orange	3	1
12	SalesPerson-K	Apple	6	3
13	SalesPerson-L	Orange	20	3

Sheet: Targets

[KevyWevz]

That is exactly what I needed. Many thanks, AliGW!