Occasional Contributor

Hello team!

I'm trying to create a formula for the following situation:


In cell B4 (capture 1) I want to display a mark from 1 to 5, on the following conditions:

5 - over 80%
4 - 70-80% 
3 - 60-70% 
2 - 50-60% 
1 - less 50% 


The percentage would be monthly updated in a different sheet, in Column G (see capture 2 below)

Capture 1


Capture 2


I tried with this formula but is not showing what I need. I'm sure something is wrong. 


=IFS(AND('Dashboard Needs'!B2="July",
OR('Dashboard Needs'!G2>=80%,'Dashboard Needs'!G2<100%)),"5",
OR('Dashboard Needs'!G2>=70%,'Dashboard Needs'!G2<80%),"4",
OR('Dashboard Needs'!G2>=60%,'Dashboard Needs'!G2<70%),"3",
OR('Dashboard Needs'!G2>=50%,'Dashboard Needs'!G2<60%),"2",
OR('Dashboard Needs'!G2>=0%,'Dashboard Needs'!G2<50%),"1",
TRUE,"no data")


Could someone please give me a hand?


Thank you in advance!

6 Replies

@gretacristina You can try XLOOKUP() function. See the attached file.

=IF('Dashboard Needs'!B2="July",XLOOKUP('Dashboard Needs'!G2,{0.8,0.7,0.6,0.5,0.1},{5,4,3,2,1},"no data",-1),FALSE)




Hi @Harun24HR, thank you so much for your reply. I copied the formula to my Excel file and for some reason I get the #NAME? error. It happens the same when I'm changing the percentage in the file you sent me.
Can you check your excel version? XLOOKUP() only exist on Microsoft-365.
best response confirmed by gretacristina (Occasional Contributor)
Please modify with simple formula like,
=IF(B2="JULY",IFS(G2>=80,5,G2>=70,4,G2>=60,3,G2>=50,2,G2<50,1,TRUE,"No Data"))
Incase of percentage you can add percentage symbol or 0.XX Decimal in the formula.
Greater than and equal to 80% is 5 and Less than 50 is 1,
Please do remember inverted commas "" is applicable only to text not number.
It worked! Thank you so much!
That's probably why it would not work. Thank you for the help!