# How can I insert a value in a cell depending on the value of another cell? [closed]

I have a spreadsheet where column A holds several values ;e.g. MU, AU, EU, MD, AD, ED, CLOSED. "U" stands for "Upstairs", "D" for "Downstairs".

I wish to execute the following pseudocode:

IF cell A'n' = MU or AU or EU THEN write "upstairs" to cell B'n' ELSE IF cell A'n' = MD or AD or ED THEN write "downstairs" to cell B'n' ELSE write "other" to cell B'n' ENDIF ENDIF

I've tried several versions of this code - =IF(D2="DU"or"NU","up","down") - but that gives me Err:508, so I suspect the problem is my ignorance of the proper syntax. Can anyone rectify my ignorance, please? Or point me to a good tutorial, at least.

TIA

edit retag reopen merge delete

### Closed for the following reason question is not relevant or outdated by Alex Kemp close date 2015-10-26 22:09:11.418521

Sort by » oldest newest most voted

I think the better way it is doing with VLOOKUP()

B2: =IFNA(VLOOKUP(RIGHT($A2;1);{"U"|"Up";"D"|"Down"};2;0)&"stairs";"Other") you need LibreOffice version 4.0 or above because the IFNA() function. For a lower version it is a bit long formula: B2: =IF(ISNA(VLOOKUP(RIGHT($A2;1);{"U"|"Up";"D"|"Down"};2;0));VLOOKUP(RIGHT(\$A2;1);{"U"|"Up";"D"|"Down"};2;0)&"stairs";"Other")

You need to set up the separators such it are defined in:

more

Good answer, but my data sample has led you astray. My database also has 2 _and_ 3 character codes, e.g. AFD and ADD. So the substring operation doesn't work. By the way, the colour highlighting of the formula in the cell: what information can I glean from that?

( 2013-07-29 21:27:47 +0100 )edit

What's the problem for extend/adapt the formula to your needs?. I think you know also it is possible with VLOOKPU() use external references, as long and diverse as you can need. If you can not find how, let us know a sample of your data, and maybe someone can find how to do it.

( 2013-07-29 23:32:59 +0100 )edit

## Stats

Seen: 2,794 times

Last updated: Jul 28 '13