Index or Xlookup 2 criteria, but 1 of them includes >=

[amyj22x3]

I need to find a way to match the Agent Type and ACHIEVED goal in a different sheet, but the payouts are as follows:

The problem is most of the time an agent hits their goal, but their achieved goal falls between 2 target goal tiers. So the problem is their achieved goal is 6,600 I need the returned incentive to be 100 since they didn't make it to the 2nd tier of 7,000

I've tried all combinations of index(match, vlookup, xlookup and so on, but because I have to match the Agent Type exactly, but the Goal approximately I can't get the right answer.

Agent Type	Goal Target	Incentive
1 MO	6,500	100
1 MO	7,000	110
Agent	26,000	200
Agent	29,000	300
Sr. Agent	27,000	250
Sr. Agent	29,000	300

 

[hagia_sofia]

The Description is a little bit confusing, but XLOOKUP has a match mode argument. So, without references it could look like: =XLOOKUP(goal target value&agent type value,goal target range&agent type range;incentive range,,-1). The -1 argument will return exact match or next smallest match, or 6,500 if you goal target value is 6,600.

 

[awoohaw]

What version of excel are you using? Please update your profile here so that the version of excel you use shows up on your posting button? (See how 365 is under my button on the left?)

 

[amyj22x3]

What version of excel are you using? Please update your profile here so that the version of excel you use shows up on your posting button? (See how 365 is under my button on the left?)

Updated, I'm using 365.

 

[amyj22x3]

The Description is a little bit confusing, but XLOOKUP has a match mode argument. So, without references it could look like: =XLOOKUP(goal target value&agent type value,goal target range&agent type range;incentive range,,-1). The -1 argument will return exact match or next smallest match, or 6,500 if you goal target value is 6,600.

Apologies. Yeah, I tried using the -1 at the end of the xlookup, but it still didn't work.

 

[hagia_sofia]

Could you elaborate more on what you are trying to calculate?

 

[amyj22x3]

Could you elaborate more on what you are trying to calculate?

I calc sales incentives on a monthly basis. For the last couple of years I've used a nested If(and formula to look at the Agent Type and their sales (achieved goal points), but my boss can't understand the formula, so he's forcing me to change it. The table below shows what they'll earn if they achieve each Target Goal, however because life is life, their sales are never going to be exact to the Goal Target tiers. The Agent Type matches perfect, but if Agent A who is a 1 MO agent gets 6,600 then their Incentive Payout should be 100, not 110.

Agent Type	Target Goal	Incentive
1 MO	6,500	100
1 MO	7,000	110
Agent	26,000	200
Agent	29,000	300
Sr. Agent	27,000	250
Sr. Agent	29,000	300

 

[awoohaw]

try this:

Book1
	A	B	C	D	E	F
1	Agent Type	Target Goal	Incentive		Agent Type:	1 MO
2	1 MO	6,500	100		Sales	6,600
3	1 MO	7,000	110		Incentive:	100
4	Agent	26,000	200
5	Agent	29,000	300
6	Sr. Agent	27,000	250
7	Sr. Agent	29,000	300
Sheet2

Cell Formulas
Range	Formula
F3	F3	=LET(vl,FILTER($A$2:$C$7,$A$2:$A$7=$F$1,""),s,CHOOSECOLS(vl,2),I,CHOOSECOLS(vl,3), XLOOKUP($F$2,s,I,"",-1))

Cells with Data Validation
Cell	Allow	Criteria
F1	List	=$A$2:$A$7

 

[amyj22x3]

try this:

Book1
	A	B	C	D	E	F
1	Agent Type	Target Goal	Incentive		Agent Type:	1 MO
2	1 MO	6,500	100		Sales	6,600
3	1 MO	7,000	110		Incentive:	100
4	Agent	26,000	200
5	Agent	29,000	300
6	Sr. Agent	27,000	250
7	Sr. Agent	29,000	300
Sheet2

Cell Formulas
Range	Formula
F3	F3	=LET(vl,FILTER($A$2:$C$7,$A$2:$A$7=$F$1,""),s,CHOOSECOLS(vl,2),I,CHOOSECOLS(vl,3), XLOOKUP($F$2,s,I,"",-1))

Cells with Data Validation
Cell	Allow	Criteria
F1	List	=$A$2:$A$7

 

[awoohaw]

The LET function allows you to use temporary names for references.

=LET(vl,FILTER($A$2:$C$7,$A$2:$A$7=$F$1,""),s,CHOOSECOLS(vl,2),I,CHOOSECOLS(vl,3),
XLOOKUP($F$2,s,I,"",-1))

The syntax of LET is pairs of arguments followed by a final single argument.
The odd number in sequence arguments are the assigned names (or in the last case a final calculation).
The even number in sequence arguments are the formulas that are assigned to the assigned name.

the vl is the filtered data, filtering columns A:C for the agent type selected in cell F1
s the s is the 2nd column of the filtered data (gets the filtered sales column for what ever agent type is in cell F1.
the l is the 3rd column of the filtered data (gets the incentive column for the agent type)