How do I base the value of a cell on the result of a multiple choice in another cell?

I need to base the value of a cell on the result of a multiple choice in another cell (5 choices with 5 different numeric values). Have tried every variation of the IF Function I can think of, but clearly getting the format wrong.

Using Calc on LibreOffice v4.1.5.3 with Windows 8.2

You should define a range (column) of valid inputs and a corresponding range with the values to assign. Then use the standard functions MATCH() and INDEX() [or, maybe CHOOSE()]. For applicability and user-friendliness you may also draw upon IFERROR().

See attached for details. (Originally: ask34983ChooseNumberByText001.ods)

You could concatenate the result of the 5 cells and then have a vlookup function search on a result table… Without further details or a sample file it is a little hard to guess and provide better help…

Alternatively you can have a complex IF sequence, e.g.


Many thanks for the advice. Finally found that the VLOOKUP function worked best for me. If everybody can read this comment, please accept my thanks for all your advice. It was all helpful and I learned a great deal following it all up.

Hi Pedro,
Many thanks for the response. My apologies, but I realise I didn’t explain myself too well. My drop down choices are text - ‘never’, ‘daily’, ‘weekly’, ‘monthly’ and ‘annually’, to which I want to attribute the values ‘0’,‘365’, 52’,‘12’,& ‘1’ to allow annual calculations dependent on the frequency selected.
Again my apologies - I should have been more specific in the first place.

@JD1, this is not really a Calc problem, it’s a general spreadsheet knowledge :wink: So the value you want to get is the SUM of the 5 choices?

I just want the the value of a second cell to be determined by the choice in the first cell e.g. IF C4=Never Let D4 =0; IF C4 =Daily Let D4=365; IF C4=Weekly LET C4=52 etc. I appreciate that this isn’t written using the correct format as would be entered in the target cell ( =IF(C4=‘Weekly’,52) ), but what i am trying to do is get the formula in the cell to consider the 5 text options and enter the appropriate numeric value for the choice made in the one specific cell (in my example above,D4).

I assume, that you get your drop-down-list from validity. If yes, put your values pair into a separate range, for example into Sheet2.A1:B5. So that you get the entries

never    0
daily    365
weekly   52
monthly  12
annually 1

In the validity dialog choose Allow “cell range” and select the range A1:A5. If the drop-down is in Sheet1.A1 for example and you want the number value in Sheet1.A2, then the formula in Sheet1.A2 is =LOOKUP(A1;Sheet2.A1:A4;Sheet2.B1:B4)

Another way is to use a form control “List box”. Enter the list in the property tab General into item “List entries”, (new line with Ctrl Enter). And in the property tab “Data” bind it to cell A1 for example and set cell content of linked cell to Position. Put the form control directly on the cell A1 to hide the cell value with the drop-down list. Then you can use the position value for the formula =CHOOSE(A1;0;365;52;12;1) in cell A2.