Conditional statement in excel about showing text conditionally

Discussion in 'Programming/Html' started by gdeliana, Jun 8, 2010.

  1. gdeliana

    gdeliana Member Guru

    Messages:
    130
    Likes Received:
    0
    GPU:
    Sapphire HD5570 512 GDDR5
    Now my problem is like this:

    I want that in a cell B2 (this is example) to be showed a certain text when the value of in cell G5 is 1 and if G5 has the value of 2....B2 will show another text.
    So if G5 is 1 -> B2 shows text1
    G5 = 2 -> B2 shows text2
    G5 = 3 -> B3 shows text3

    I'm not a coder :D

    PLZZZZZ HEEELP i need it for my seminar work!!
     
    Last edited: Jun 8, 2010
  2. PuddingWagon

    PuddingWagon Maha Guru

    Messages:
    1,087
    Likes Received:
    0
    GPU:
    Gigabyte GTX 670
    As far as I know there are two ways to do this (usable 'code' is in bold, the links give better explanations).

    First way is using LOOKUP, which is pretty simple.
    Code:
    =LOOKUP(<lookup value>, <lookup vector>, <result vector>)
    Basically this just takes the lookup value, finds that in the lookup vector then uses that same index (element number) for the result vector.
    Think of a table of values:
    Code:
    Lookup Vector: {   1,       2,       3   }
    Result Vector: {"text1", "text2", "text3"}
    Now the actual code:
    Code:
    [B]=LOOKUP(G5,{1,2,3},{"text1","text2","text3"})[/B]
    Would produce "text1" if G5 was 1, "text2" if G5 was 2...
    However this has a problem that if G5 isn't in the lookup vector (e.g. G5 is 4), then LOOKUP will still give you an output. See the above link for more detail.



    The other way would be nested IF statements, which can give you an obvious invalid message when G5 wasn't as expected (e.g. G5 is 4).
    Code:
    =IF(<statement to evaluate>, <output if true>, <output if false>)
    Nest three of these and you get your B2 code. Nesting basically chains the ifs together, where the false case executes the next if.
    Code:
    if ( G5 is 1 ) then
      output = "text1"
    else if ( G5 is 2 ) then
      output = "text2"
    else if ( G5 is 3 ) then
      output = "text3"
    else
      output = "INVALID"
    end if
    Which in Excel would be:
    Code:
    [B]=IF(G5=1,"text1",IF(G5=2,"text2",IF(G5=3,"text3","INVALID")))[/B]
    Matching to a non-number would require G5="match text" I think.
     

Share This Page