Forum Discussion

srsmith1978's avatar
srsmith1978
Copper Contributor
Aug 16, 2022

How to assign different values to different text words in single cell?

Hello,  I hope you can help me.

 

I would like to have a different number value appear in a cell depending on what word is choosen from a drop down menu in another cell.  The words with the values are below: -

 

Drop Down SelectionValue Assigned
Weak4
Limited3
Reasonable2
Strong1
N/A 0

 

My excel formula knowledge is OK at best.  So far I can get the formula to react to one word only: - 

=IFERROR(IF(SEARCH("Weak",D13:D21),1),0)

 

I would like the value cell to change value depending on what word is selected.

 

Thank you

 

Steve

Formulas and Functions

6 Replies

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 16, 2022

    srsmith1978 

    If you list the attributes in numerical order XMATCH could be used directly

    = IFERROR(
      XMATCH(selection, {"Strong";"Reasonable";"Limited";"Weak"}),
  0)

    A Lambda function would tidy the functionality somewhat

    WorksheetFormula
= AssignValueλ(selection)

AssignValueλ
= LAMBDA(s,
    IFERROR(
      XMATCH(s, {"Strong";"Reasonable";"Limited";"Weak"}),
    0)
  )

     

  • srsmith1978's avatar
    srsmith1978
    Copper Contributor
    Aug 16, 2022

    srsmith1978 

     

    Thanks for reply but these 2 do not seem to work.

     

    Let me explain what I need.

     

    I have spreadsheet.  In left column I have a drop down menu from which you can select Weak, Limited, Reasonable etc.  I want to assign each word a specific value which id like to appear in the next cell on the row.  See mockup below: -

     

    Ive tried Nested IF formula with also no luck.

     

    Is there another way?

     

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Aug 16, 2022

      srsmith1978 

      =IFERROR(INDEX($F$2:$F$6,MATCH(A2,$E$2:$E$6,0)),"")

      You can try this formula for the data layout of the example file. This formula is easier than nested IF. The dropdowns are in range A2:A6 in the example and the formula is in range B2:B6.

       

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Aug 16, 2022

    srsmith1978 

    =INDEX($B$2:$B$6,MATCH(D2,$A$2:$A$6,0))

    You can try this formula for the data layout of the example. The dropdown is in cell D2.

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 16, 2022

    srsmith1978 

    Select the range shown in your post, from the cell with "Weak" to the cell with 0 (.e. 5 rows by 2 columns).

    Click in the name box on the left hand side of the formula bar.

    Type the name Scores and press Enter.

    You have assigned the name Scores to the selected range.

     

    Let's say you have a drop-down with Weak, Limited etc. in D13.

    In E13, enter the formula

    =IFERROR(VLOOKUP(D13,Scores,2,FALSE),"")

    You can fill this down if required.

Resources