+ Reply to Thread
Results 1 to 8 of 8

Matching data from an adjacent cell based on its value

  1. #1
    Registered User
    Join Date
    03-06-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    5

    Unhappy Matching data from an adjacent cell based on its value

    Hi, if someone can assist me it will be greatly appreciated.

    I need to automatically insert a value to a cell based on a word in an adjacent cell.

    Using =IF(ISNUMBER..... and =IF(ISERROR.... I have managed to find a single word from a cell (cell F2 - with a dropdown consisting of 2 options) and add a value to the adjacent cell but I need to add a different value to the adjacent cell based on the word in F2:

    A) Cell F2 is a dropdown cell with "CSE" or "JCP"

    B) If CSE is selected, the adjacent cell (G2) needs to be automatically populated with 2.50. If JCP is selected, G2 needs to be populated with 1.67

    D) I also need to know what to do if I add a 3rd option to the dropdown which needs a completely different value.

    E) If cell F2 is blank, then cell G2 should also remain blank.

    Please assist.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Matching data from an adjacent cell based on its value

    In G2
    =IF($F$2="CSE", 2.5,IF($F$2="JCP",1.67,""))
    for 3rd option
    =IF($F$2="CSE", 2.5,IF($F$2="JCP",1.67,IF($F$2= 3rd opt, #.#,"")))
    Is that what you were looking for?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Matching data from an adjacent cell based on its value

    Try: =IF(F2="","",LOOKUP(F2,{"CSE","JCP"},{2.5,1.67}))

    Extend Lookup and Result vectors as required.

    Dom
    Last edited by Domski; 03-06-2012 at 11:22 AM.
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  4. #4
    Registered User
    Join Date
    03-06-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Matching data from an adjacent cell based on its value

    Quote Originally Posted by ChemistB View Post
    In G2
    =IF($F$2="CSE", 2.5,IF($F$2="JCP",1.67,""))
    for 3rd option
    =IF($F$2="CSE", 2.5,IF($F$2="JCP",1.67,IF($F$2= 3rd opt, #.#,"")))
    Is that what you were looking for?
    Thanks, but if I use the 1st option then the value does not appear when i use CSE form the dropdown. It stays blank.
    Also I have a calculation at the end of the row which (when the value is left blank ) shows #VALUE!

  5. #5
    Registered User
    Join Date
    03-06-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Matching data from an adjacent cell based on its value

    Thanks Domski, this seems to work well.
    I am using the "value" of CSE or JCP in a formula at the end of the row, as with the solution from ChemistB, when there is no value in column F, my calculation in column now shows #VALUE!

    Is there a way to ensure that this is not displayed when I have not entries in F and G columns?

  6. #6
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Matching data from an adjacent cell based on its value

    Rather than returning a blank string "" when there is no entry can you return 0?

    Dom

  7. #7
    Registered User
    Join Date
    03-06-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Matching data from an adjacent cell based on its value

    Yes, that should be fine.

  8. #8
    Registered User
    Join Date
    03-06-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Matching data from an adjacent cell based on its value

    Ok, works great. Thanks for the support, much appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1