Having an excel sheet with a table as below:
same page with a different table. How can I return the comment to another table.
CodePudding user response：
As you specifically ask for a formula with IF this should work:
=IF(A2<60,"Fail",IF(A2<70,"Average",IF(A2<80,"Good",IF(A2<90,"Very good",IF(A2<101,"Excelent")))))
Drop it in B2 and drag it down.
Result:
CodePudding user response：
I recommend rearranging your score data like this:
| Score <= | Return value |
|---|---|
| 59 | Fail |
| 69 | Average |
| 79 | Good |
| 89 | Very good |
| 100 | Excellent |
Then you can use XLOOKUP:
=XLOOKUP(A13:A22,A2:A6,B2:B6,"Out of score range",1)
If you have older Excel use:
=INDEX($B$2:$B$6,MATCH(TRUE,$A$2:$A$6>=A13,0))
This requires being entered with ctrl shift enter.
This indexes the range B$2:B$6 (use $ to lock the row references when dragging the formula) and shows the first row where the MATCH condition is met:
Range A$2:A$6 (note the $ again) being greater than or equal to the value in A13 (do not use $ here, so the row value will change if we drag the formula up/down)