I need help writing the formula for the ranking column, using IF and AND function. The data is an example I just need the formula.

n. Write a Nested IF function (you can combine it with AND function) in Ranking worksheet, Ranking column to create the following conditions and outputs. Table 1. "Nested IF" and "AND" Condition 1 Condition 2 Output Scenario Cust_Cat_ Shift Ranking 1 Guest Morning 1 2 Guest Afternoon 2 3 Employee Any category 3 4 Else (False statement) 4 Start from the first scenario IF(AND(condition 1, condition 2),?,IF()..., M8 fx L Ranking A B D E F. G H 1 Trans ID SKU Pay_Type Trans Qty Emp_ID Cust_Cat SKU_Cat Shift Coupon_Amt Retail 2 1000234 X349R39 Cash 3 38 Employee Coffee Morning 25% $ 1.75 3 1000235 R483P24 Cash 2 38 Guest Coffee Morning 0% $ 4.50 4 1000236 F833K19 Ccard 2 38 Guest Food Morning 20% $ 3.25 5 1000237 R483P24 CCard 3 24 Guest Coffee Morning 0% $ 4.50 6 1000238 X349R39 CCard 4 24 Employee Coffee Morning 25% $ 1.75 7 1000239 F833K19 Cash 1 24 Guest Food Morning 0% $ 3.25 8 1000240 C584123 CCard 2 24 Guest Coffee Morning 0% $ 5.00 9 1000241 C584123 Cash 3 24 Guest Coffee Morning 0% $ 5.00 10 1000242 F833K19 CCard 4 24 Employee Food Morning 25% $ 3.25 11 1000243C483T89 CCard 2 38 Guest Food Morning 0% $ 4.25 12 1000244 F375P67 CCard 3 38 Guest Food Morning 0% $ 3.25 13 1000245 X487P48 Cash 3 38 Employee Coffee Morning 25% $ 2.25 14 1000246 C584123 CCard 4 38 Guest Coffee Morning 0% $ 5.00 15 1000247 C584123 CCard 2 38 Guest Coffee Morning 0% $ 5.00 16 1000248 C584123 CCard 3 38 Guest Coffee Morning 0% $ 5.00 17 1000249 R483P24 CCard 4 38 Guest Coffee Morning 0% $ 4.50 18 1000250 R483P24 Ccard 4 38 Guest Coffee Morning 20% $ 4.50 19 1000251 F375P67 Cash 2 38 Guest Food Morning 0% $ 3.25 20 1000252 R483P24 Cash 1 24 Guest Coffee Morning 0% $ 4.50 21 1000253 F375P67 Cash 4 24 Guest Food Morning 0% $ 3.25 22. 1000254 F375P67 Ccard 2 24 Guest Food Morning 0% $ 3.25 23 1000255 C483T89 CCard 3 24 Guest Morning 0% $ 4.25 24 1000256 X733R14 Ccard 1 15 Guest Gift Morning 15% $22.00 25 1000257 C483T89 CCard 4 15 Guest Food Morning $ 4.25 26 1000258 C584123 CCard 4 15 Guest Coffee Morning 0% $ 5.00 27 1000259C584123 CCard 4 15 Guest Coffee Morning 0% $ 5.00 28 1000260 F833K19 CCard 2 38 Guest Food Morning 0% $ 3.25 29 1000261 F833K19 CCard 4 38 Guest Food Morning 0% $ 3.25 30 1000262 C584123 CCard 4 38 Guest Coffee Morning 0% $ 5.00 31 1000263 C584123 CCard 6 38 Guest Coffee Morning 0% $ 5.00 32 1000264 C584T23 CCard 2 38 Guest Coffee Morning 0% $ 5.00 33 1000265 C483189 CCard 2 38 Employee Food Morning 25% $ 4.25 34 1000266 C483T89 Cash 2 38 Employee Food Morning 25% $ 4.25 35 1000267 C483T89 Cash 2 38 Guest Food Afternoon 0% $ 4.25 36 1000268 IC584T23 CCard 15 Guest Coffee Afternoon 0% $ 5.00 Food 0%