Office 365 - Return text in range for criteria

[Broken1Winggs]

Hello all,

It's my first post here.
I have little problem how to create formula to return correct answer according to value in cell.

I have three opinions for each group of three. I need to create formula to return text according to score respectfully for each of groups.
Already created matrix with score ranges and return texts but don't have any good idea how to create last part.
Please see attached screenshot.
Screenshot excel.jpg
Hope someone can help me with that problem.

Thank you in advance.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[Glenn Kennedy]

One way:
=LET(a,INDEX($C$17:$J$19,,MATCH(C3,$C$16:$J$16,0)),F,TEXTBEFORE(a,"-")+0,T,TEXTAFTER(a,"-")+0,INDEX($D$17:$D$19,MATCH(1,(D3>=F)*(D3<=T),0)))

[AliGW]

Here's another that requires no lookup tables (all built in):

=LET(ga,VSTACK({0,23,50},{"Low","Medium","High"}),gb,VSTACK({0,23,68},{"Low","Medium","High"}),gc,VSTACK({0,43,74},{"Low","Medium","High"}),c,CHOOSE(--RIGHT(C5,1),ga,gb,gc),HLOOKUP(D5,c,2,1))

weher C5 is a group name and D5 is a score.

[Broken1Winggs]

Thank you.

I will look at regales and also stick up your advise.

[Broken1Winggs]

One way:
=LET(a,INDEX($C$17:$J$19,,MATCH(C3,$C$16:$J$16,0)),F,TEXTBEFORE(a,"-")+0,T,TEXTAFTER(a,"-")+0,INDEX($D$17:$D$19,MATCH(1,(D3>=F)*(D3<=T),0)))

Thank you, I will try using this in my work.

[Broken1Winggs]

Here's another that requires no lookup tables (all built in):

=LET(ga,VSTACK({0,23,50},{"Low","Medium","High"}),gb,VSTACK({0,23,68},{"Low","Medium","High"}),gc,VSTACK({0,43,74},{"Low","Medium","High"}),c,CHOOSE(--RIGHT(C5,1),ga,gb,gc),HLOOKUP(D5,c,2,1))

weher C5 is a group name and D5 is a score.

And I will also try this method and see which one will be easier for me to understand and implemented.
Thank you for help.

[AliGW]

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.