If Cell Contains Specific Text Then Return Value and Sum Row for Scoresheet

Question

I am trying to create a formula that takes a selection of choices from a cell and turns it into a "score", then at the end of the row it adds the score up. I know this is not correct but it would be along the lines of

=(IF(A2="Red", 1, "")), (IF(A2="Blue", 2, "")), ,(IF(A2="Green", 3, "")), (IF(A2="Yellow", 4, "")), (IF(A3="Oval", 1, "")), (IF(A3="Octagon", 2, "")), (IF(A3="Triangle", 3, "")), (IF(A3="Square", 4, ""))...

and then at the end SUM A2:D2.

Can anyone help me with this? Thank you!

Answer 1

Here is an explanation to the formula which I have posted above in comments with few other alternatives, which you may give a try, if you are using O365

• Formula used in cell E2

=SUM(LOOKUP(A2,{"Blue","Green","Red","Yellow"},{2,3,1,4}),
LOOKUP(B2,{"Octagon","Oval","Square","Triangle"},{2,1,4,3}),
LOOKUP(C2,{"Bank","Church","Playground","School"},{3,1,4,2}),
LOOKUP(D2,{"Bank Teller","Doctor","Martial Arts Instructor","Teacher"},{4,1,3,2}))

---

So how does LOOKUP() Function works?

• The LOOKUP() function looksup a value in a one-column or one-row range, and retrieve the value from the same position in another one-column or one-row range.
• The LOOKUP() function has two forms, one is vector and another is array.
• The LOOKUP() function accepts three arguments: lookup_value, lookup_vector, and result_vector.
• The first argument is lookup_value which is the value to look for.
• The second argument is lookup_vector which is a one-row, or one-column range to search.
• While the third argument which is result_vector, is a one-row, or one-column range of results.
• Result_vector is optional. When result_vector is provided, LOOKUP() Function locates a match in the lookup_vector, and returns the corresponding value from result_vector.
• If result_vector is not provided, LOOKUP() function returns the value of the match found in lookup_vector.
• LOOKUP() Function assumes that lookup_vector is sorted in ascending order.
• LOOKUP() Function is not case-sensitive.

---

However I assume you have access to O365 then you may try using the [XLOOKUP()][2] Function as well, XLOOKUP() is a much faster function than anyother lookup function

• Formula used in cell G2

=SUM(XLOOKUP(A2,{"Blue","Green","Red","Yellow"},{2,3,1,4}),
XLOOKUP(B2,{"Octagon","Oval","Square","Triangle"},{2,1,4,3}),
XLOOKUP(C2,{"Bank","Church","Playground","School"},{3,1,4,2}),
XLOOKUP(D2,{"Bank Teller","Doctor","Martial Arts Instructor","Teacher"},{4,1,3,2}))

---

One more way is to use LAMBDA() Function, to create a custom, reusable function and refer them by a friendly name,

• Formula used in cell F2

=SumScores(A2,B2,C2,D2)

Where,

SumScores => 

=LAMBDA(a,b,c,d,
SUM(XLOOKUP(a,{"Blue","Green","Red","Yellow"},{2,3,1,4}),
 XLOOKUP(b,{"Octagon","Oval","Square","Triangle"},{2,1,4,3}),
 XLOOKUP(c,{"Bank","Church","Playground","School"},{3,1,4,2}),
 XLOOKUP(d,{"Bank Teller","Doctor","Martial Arts Instructor","Teacher"},{4,1,3,2})))

LAMBDA() Function used in Name Manager with a Defined Name as SumScores with syntax as

=SumScores(a,b,c,d)

---

EDIT

OP mentioned in comments : So XLOOKUP() works perfectly, however, I just found out I am using Slack for my integration and can only use Google Sheet - nooooooo!!!

• Formula used in cell E2

=ARRAYFORMULA(SUM(VLOOKUP(A2,{"Blue",2;"Green",3;"Red",1;"Yellow",4},2,0),
VLOOKUP(B2,{"Octagon",2;"Oval",1;"Square",4;"Triangle",3},2,0),
VLOOKUP(C2,{"Bank",3;"Church",1;"Playground",4;"School",2},2,0),
VLOOKUP(D2,{"Bank Teller",4;"Doctor",1;"Martial Arts Instructor",3;"Teacher",2},2,0)))

---

Answer 2

Here is a formula solution based on the OP's layout.

In E2, array ("Ctrl"+"Shift"+"Enter") formula copied down :

=SUM(FILTERXML("<a "&SUBSTITUTE(TEXTJOIN("' ",,SUBSTITUTE(A$9:D$12," ","_")),"=","='")&"'/>","//@"&SUBSTITUTE(A2," ","_")&"|//@"&B2&"|//@"&C2&"|//@"&SUBSTITUTE(D2," ","_")))