Alternative way for long formula typed with IFS

[MasmaAbdulhamidli]

Hi. I have two sheets that I have to get data from each other. I have multiple criteria and I want to downsize my formula length. If any alternative way you know please enlighten me. In the first sheet I have 2722 rows. My criteria are performance score of employees, their score of grades and percentage of performance. I need to get value from another sheet if their performance score is 5, score of grade is 1280 and percentage below 80% get from second sheet their minimum wage limit + their promotions value from first sheet and write in first sheet. If percentage is between 80-100% then get medium wage limit from second sheet + their promotions value from first sheet, If percentage is between 100-120% then get maximum wage limit + their promotions value from first sheet. If someone's percentage limit is above 120% or their performance score is 1 then do nothing and get their data from annual year wage column. I have 42 score of grades and each one has 3 criteria: minimum, medium and maximum. For 2722 employers I need to get data. I wrote formula IFERROR, IFS and AND functions. But it is so long formula and when I want to save excel workbook it says "One or more formulas in this workbook are longer than the allowed limit of 8192 characters. Cell AC1000 contains one of these formulas". And really after 1000th row formula seems different but calculates right.
I share example with you. Thanks a lot!

 

[Alex Blakenburg]

Your logic is incomplete so we can only guess.
ie your logic only covers Performance = K3 = 5. How does 1-4 influence the number returned ?
Does a score of 1150 return the 1120 rate ?

See if this gives you any ideas.
You would need to sort your rate table in ascending order (it is curently in descending order)

20240530 Lookup 2D MasmaAbdulhamidli.xlsx
	A	B	C	D	E
1	Score of Grade	Col B	Performance	Percentate of Performance	Formula
2	1281	67356	5	110.00%	211111
3	1121	57432	5	83.00%	155687
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=IF(C2=$K$3,IF(D2>120.01%,B2,VLOOKUP(A2,Sheet2!$B$4:$E$10,IFS(D2>100.01%,4,D2>80.01%,3,D2<80.01%,2),TRUE)),"Not 5")

Rate Table Ascending order

20240530 Lookup 2D MasmaAbdulhamidli.xlsx
	B	C	D	E
1		0-80	80-100	100-120
2
3	Score of Grades	Minimum	Medium	Maximum
4	1000	111205	139006	166807
5	1120	124549	155687	186824
6	1280	140741	175926	211111
Sheet2

 

[MasmaAbdulhamidli]

Thank you for reply. I have fix performance scores that 1 and 5. And I have fix Score of Grades. I wrote the formula if performance = 5 then do that, if it is not true there is only option is 1 and do for 1's. I have many people in 1000th grade but their percentage are different. So I have 2722 people with different scores and performance and percentage. I need to write formula all of them

 

[Alex Blakenburg]

Sort your sheet 2 ascending try the formula I gave you, then give examples of what worked and what didn't.

 

[MasmaAbdulhamidli]

Sort your sheet 2 ascending try the formula I gave you, then give examples of what worked and what didn't.

thank you very much, it worked.

 

[Alex Blakenburg]

You're welcome, thanks for the feedback.