IF, AND, OR HELP

[gretacristina]

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!

[#2]

@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)

[#3]

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.

[#4]

Can you check your excel version? XLOOKUP() only exist on Microsoft-365.

[#5 · Accepted solution]

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.

[#6]

It worked! Thank you so much!

[#7]

That's probably why it would not work. Thank you for the help!