Convert text values to int type values in google sheets from another sheet and sum it up in a sequen-CodePudding

Need some help on converting string text to int values.

Here is what I tried:

=IF(Survey!J2,"Myself",1,"My team",5,"Stakeholders",10,"Project",15,"") Survey!T2,"Myself",1,"My team",5,"Stakeholders",10,"Project",15)

Need it to look for string value in sheet2 named Survey! and add this value in sheet 1 name calculator in row K2.

Expected output would be 2, if user selected myself both times. The lookup value is called Impact in sheet two row 1, is there a way I could get the code to look for "Impact" in Sheet 2 and convert the string text to numbers, depending on what the user selected and then perform a sum for all of the impact columns.

CodePudding user response：

try:

=IFNA(VLOOKUP(Survey!J2, {"Myself",1;"My team",5;"Stakeholders",10;"Project",15}, 2, 0)) 
 IFNA(VLOOKUP(Survey!T2, {"Myself",1;"My team",5;"Stakeholders",10;"Project",15}, 2, 0))