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

 

[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

OMG, this worked brilliantly! Thank you SO MUCH!

 

[awoohaw]

OMG, this worked brilliantly! Thank you SO MUCH!

You're welcome.
Best Wishes!

PS you may need to fine tune the XLOOKUP function with a 0 instead of "" for the 'if not found' argument. Otherwise you'll get blanks for folks that do not make the bottom threshold of sales amount.

 

[amyj22x3]

You're welcome.
Best Wishes!

PS you may need to fine tune the XLOOKUP function with a 0 instead of "" for the 'if not found' argument. Otherwise you'll get blanks for folks that do not make the bottom threshold of sales amount.

And that's my desired outcome. You either hit goal, or you don't. You have no idea how great I feel right now! I've been struggling for hours today and yesterday. Never, would I have thought that formula on my own.