High/Med/Low in one cell to turn into a 5, 3, and 1 in another cell - HELP!

[Boorayin]

Have attached a picture to help explain.

I need a team member to do an assessment of L/M/H on a task (Col S). And, I need that to become a number (am assuming I'd need a Col T that I'd hide?) that then gets multiplied by a weight (Col U that'd also be hidden) to give me a total score (Col V). Not sure how to do this - IF/THEN's? Help, please!! 2020-10-20_19-49-20.jpg

[bebo021999]

Depend on instruction in cell S1 (1=H,...,5=L)

With character is in S2

In U3:

=LOOKUP(2,1/({"H";"M";"L"}=S2),{1;3;5})

Change {1;3;5} into {1;2;3}, if you would like to re-assign 1,2,3 to H,M,L

[Boorayin]

Thanks so much for posting back!! I pasted that into U3, but it errored with "Did not find 2 in LOOKUP evaluation" (I'm actually having to do this in Google Sheets, but I'm assuming the formula is the same as in Excel). Yes, I'd want H/M/L to equal 1, 3, and 5. Thanks!!

[Logit]

.
This is one method :

In Col T :

Please Login or Register  to view this content.

In Col V :

Please Login or Register  to view this content.

As for the rest of your question ("...that gets added to some other scores.") ... I haven't a clue what you are talking about.

[Boorayin]

Thanks a bunch from a fellow (previous) North Carolinian!! I got "FALSE' when I put that into Col T - ? They're just picking L, M, or H from a drop-down in S1. And for the rest, I removed that as it was indeed confusing.

[bebo021999]

Thanks so much for posting back!! I pasted that into U3, but it errored with "Did not find 2 in LOOKUP evaluation" (I'm actually having to do this in Google Sheets, but I'm assuming the formula is the same as in Excel). Yes, I'd want H/M/L to equal 1, 3, and 5. Thanks!!

Lookup works for me in attachment.

Another solution:

=INDEX({1,3,5},MATCH(S2,{"H","M","L"},0))

[Boorayin]

Yes! That worked. Thanks so much. My wife (who's from Vietnam) will be tickled that some random dude on the Internet in Vietnam solved my issue.