IF AND, IF OR AND?

[Alex501]

Hi I have a nested if formula to work out an agents 'status' based on revenue which works fine, however I've been asked to reduce the targets for 2 agents as they have been with us for much less time.

My original formula looke like;

=IF(E20<75000,"None",IF(E20<100000,"Iron",IF(E20<225000,"Bronze",IF(E20<575000,"Silver",IF(E20<750000,"Gold",IF(E20<1250000,"Platinum",IF(E20<1900000,"Diamond",IF(E20<2150000,"Hall of Fame",IF(E20<2600000,"Rockstar",IF(E20>=2600000,"Club President"))))))))))

I somehow need to add in that if the sales agent is Jim or Rob then to use the reduced targets. I''ve tried various incarnations of IF AND and IF OR but getting nowhere.

The targets and stauses are;

Any help would be greatly appreciated.

Thanks

Alex

 

[Peter_SSs]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Is this what you want?

25 01 21.xlsm
	D	E	F	G	H	I	J
1						Team	Jim & Rob
2					Iron	75,000	55,000
3					Bronze	100,000	75,000
4					Silver	225,000	150,000
5					Gold	575,000	350,000
6					Platinum	750,000	575,000
7					Diamond	1,125,000	875,000
8					Hall of Fame	1,900,000	1,250,000
9					Rock Star	2,150,000	1,600,000
10					Club President	2,600,000	2,000,000
11
18
19	Agent	Amount	Status
20	Ken	167,458	Bronze
21	Anne	2,500,000	Rock Star
22	Pam	76,625	Iron
23	Jim	76,625	Bronze
24	Robin	161,713	Bronze
25	Rob	1,700,000	Rock Star
26	Tom	1,700,000	Diamond
27	Jen	3,000,000	Club President
28	Don	40,000	None
Status

Cell Formulas
Range	Formula
F20:F28	F20	=IFNA(INDEX(H$2:H$10,MATCH(E20,IF(OR(D20={"Jim","Rob"}),J$2:J$10,I$2:I$10))),"None")

 

[Alex501]

Hi Peter

Apologies, XL2BB is now installed, thanks for the advice.

Your solution works perfectly, thanks very much for your assistance.

Alex

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.