Nested IF Function not working

[Neruu]

Hi I have a column of temperature and the percentage.

I want the percentage column (D) is based on the temperature column (C) where:

IF 30=7 

    31=7

    32=9

    33=9

    34=11

    35=11

Else =0

I have try to do using nested IF, but none works and gives all the value =0.

=IF(C2="30",7,IF(C2="31",7, IF(C2="32",9,IF(C2="33",9,IF(C2="34",11,IF(C2="35",11,0))))))

I also attached the a picture for references.

Thank you.

Date_Time	Day	Temperature	Percentage (%)
01-08-20 0:00	Saturday	27	0
01-08-20 1:00	Saturday	26	0
01-08-20 2:00	Saturday	26	0
01-08-20 3:00	Saturday	26	0
01-08-20 4:00	Saturday	26	0
01-08-20 5:00	Saturday	26	0
01-08-20 6:00	Saturday	26	0
01-08-20 7:00	Saturday	26	0
01-08-20 8:00	Saturday	26	0
01-08-20 9:00	Saturday	28	0
01-08-20 10:00	Saturday	29	0
01-08-20 11:00	Saturday	30	0
01-08-20 12:00	Saturday	31	0
01-08-20 13:00	Saturday	32	0
01-08-20 14:00	Saturday	32	0
01-08-20 15:00	Saturday	33	0
01-08-20 16:00	Saturday	34	0
01-08-20 17:00	Saturday	32	0
01-08-20 18:00	Saturday	31	0
01-08-20 19:00	Saturday	30	0
01-08-20 20:00	Saturday	30	0
01-08-20 21:00	Saturday	29	0
01-08-20 22:00	Saturday	29	0
01-08-20 23:00	Saturday	29	0

[#2]

@Neruu See if the attached workbook does what you need. I prefer to use a lookup table over hard-coding limits in a formula with nested IF functions.

Make sure that the temperatures are numbers, since when I copied the data into a workbook, I got texts with a trailing space. Something like "30 ".

With your original formula, you were looking for "30" which is not equal to "30 ". Hence, it always returned zero.

[#3]

@Neruu 

I prefer to avoid nesting more than 3 IFs and I don't use IFS.

Two approaches:

Option 1: Create a dedicated lookup table and use XLOOKUP:

=XLOOKUP(C2:C25,temp,percentage,0,-1)

Option 2: Use SWITCH to simplify the logic:

=SWITCH(C2:C25,30,7,31,7,32,9,33,9,34,11,35,11,0)

[#4]

Thank you @Riny_van_Eekelen and @Patrick2788 for the feedback.

Much appreciated for the solution given. I have managed to use the LOOKUP and SWITCH function on different date and time.