Trying to figure out a formula to automatically grade based on a range - for example, if the answer (number) in Column C is between...

70 - 94 = WTS
95 - 115 = EXS
116 - 140 = EXC

Sample data in column C:

76
96
95
123
115

I'd assume I'd put the formula in column D to get the outcome based on the range - so if the answer is between 70 and 94 I want column D to recognise this and put the response WTS. Following the rest of the ranges, I want to be able to use this formula for all data in the column.

Here is a link:

CodePudding user response：

try:

=MAKEARRAY(COUNTA(C2:C),1,LAMBDA(r,c,VLOOKUP(INDEX(C2:C,r),INDEX({--REGEXEXTRACT(G:G,"^\d "),REGEXEXTRACT(G:G,"[A-Z] $")}),2,1)))

-