Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Nov 2009
    Location
    The Great NW
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help on multiple conditions formula, please

    I've been trying rack my brain on how to set up the syntax for inserting text into a cell depending on what another cell contains.

    For instance... I have a series of code numbers (in a column) that I want to type in manually, (500...501... 502...etc). I would like each adjacent cell to automatically fill in with text that describes the code that was typed in. I would like the logic to follow this:

    =if(a5= "500" then gasoline otherwise if a5 = "501" then groceries, otherwise if a5 = "502" then utilities otherwise "").

    Thanks... Steve

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by blackjeep22 View Post
    I've been trying rack my brain on how to set up the syntax for inserting text into a cell depending on what another cell contains.

    For instance... I have a series of code numbers (in a column) that I want to type in manually, (500...501... 502...etc). I would like each adjacent cell to automatically fill in with text that describes the code that was typed in. I would like the logic to follow this:

    =if(a5= "500" then gasoline otherwise if a5 = "501" then groceries, otherwise if a5 = "502" then utilities otherwise "").

    Thanks... Steve

    Try: If(a5=500,"gasoline",if(a5=501,"groceries",if(a5=5 02,"utilities,"")))

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you have a longer list and run into nesting issues you can use:
    =IF(ISNA(VLOOKUP(A5,{500,"gasoline";501,"groceries ";502,"utilites"},2,0)),"",VLOOKUP(A5,{500,"gasoli ne";501,"groceries";502,"utilites"},2,0))

    Of course instead of building the arrays in the formula, you could create a datatable to look up the values...

    Steve

  4. #4
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts
    If your variables are more than say a dozen entries, you should set up a section of you workshhet as a table then use VLOOKUP, as it is quicker and easier to maintain if more variable need to be added.
    Paul Coyle
    Approach love and cooking with reckless abandon

  5. #5
    New Lounger
    Join Date
    Nov 2009
    Location
    The Great NW
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks everyone... I've never used VLOOKUP... I'll look into it.

  6. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Here is an example of how VLOOKUP might be used.

    HTH
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    New Zealand
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    An alternative is to use the choose function if the codes are all contiguous

    =IF(A5>499 and A5 <503,CHOOSE(A5-499,"gasoline","groceries","utilities"),"")

  8. #8
    New Lounger
    Join Date
    Feb 2011
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you could use an in-book (or external workbook as well) range reference to check, somewhat a quick "DB" like list of codes and related items on a named range in a specified worksheet (perhaps in a specified workbook).
    with col A having the code, (1st column) and col B (2nd) having the text you need in a range named "refRange" (which is sheet3!A1:B20 for example), use
    =VLOOKUP(A1,refRange,2,FALSE) in top column B cell, and copy the formula down the B column (grab lower right corner when cursor turns to a "plus" sign, pull down across the rows). each row reference in the formula for each row's cell will be updated with A2, A3, ... A20.
    yeppir, just like tfspry shows in the above attachment
    Last edited by brrrknee; 2011-02-24 at 20:07.

  9. #9
    Lounger
    Join Date
    Dec 2009
    Location
    Toronto, Ontario, Canada
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You could use VBA to program/write a function to respond with what you want. I did this for an unavailable financial formula when I was trying to do a calculation for determining ROI that was different than what was available within the included functions in Excel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •