+ Reply to Thread
Results 1 to 11 of 11

Function to return different values based on adjacent cell

  1. #1
    Registered User
    Join Date
    03-14-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Function to return different values based on adjacent cell

    I have a worksheet where one column shows a code followed by one of four options (Deliverable 3, IFC Engineering Workpack complete, IFC Construction Workpack, Land Access Granted - eg (McFarland D.10RP185773) Final IFC Engineering Workpack Complete) which are generated using a vlookup function. In the adjacent cell, I need to find a function which will return a letter based on which of the four optios is displayed, but as the code at the start of the text is necessary for ID, the funtion needs to look at whether the cell contains certain words. I tried using the IF Function, but can only return one result.

    I need the function to do the following:
    If cell contains Deliverables, return "a"
    If cell contains Engineering, return "b"
    If cell contains Construction, return "c"
    If cell contain Land, return "d"

    I tried =IF(ISNUMBER(SEARCH("Land",F2:F3)),"d","") but got stuck there. I cannot get my head around how to return a different value based on which of the four options appears in the cell.

    I really hope that makes sense! If anyone has a suggestion I would be really appreciative.

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Function to return different values based on adjacent cell

    you just need to continue on the path you have with nested IF's

    =IF(ISNUMBER(SEARCH("Deliverables",F2)),"a",IF(ISNUMBER(SEARCH("Engineering",F2)),"b",IF(ISNUMBER(SEARCH("Construction",F2)),"c",=IF(ISNUMBER(SEARCH("Land",F2)),"d",""))))
    Last edited by DGagnon; 03-14-2012 at 09:25 PM.
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,601

    Re: Function to return different values based on adjacent cell

    Okay, just carry on with your formula for the other words, i.e.:

    =IF(ISNUMBER(SEARCH("Deliverables",F2)),"a","") & IF(ISNUMBER(SEARCH("Engineering",F2)),"b","") & IF(ISNUMBER(SEARCH("Construction",F2)),"c","") & IF(ISNUMBER(SEARCH("Land",F2)),"d","")

    Checks for those words in F2 and returns the appropriate letter (or blank if none of them are there).

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    03-14-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Function to return different values based on adjacent cell

    Wow, that seems almost too simple! Thanks so much guys, works perfectly. Thats going to make my worksheet much easier

  5. #5
    Registered User
    Join Date
    03-14-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Function to return different values based on adjacent cell

    Can I ask another question here, or must I create a new thread? If so, my question is below. If not, let me know and I'll create a new thread.

    I am using =VLOOKUP(MAX(C3:C4),C3:D4,2,FALSE) to show me a the latest date in the the selected data. What I'd like to do is use a function to identify a set of data for the VLOOKUP function to use using the code which appears in the cell, and then get the VLOOKUP to look at only that data to return what the latest date in that set of data.

    What I am trying to achieve is a system where I can download the dates for activities at each site from our project software and show which activity have been completed dates for each site. Then I need to identify what the date of the most recently completed activity was for that site (as this would be the current status of the site) and return a letter assigned to that activity. I will then use conditional formatting to colour code the status of the site (based on the identifying letter) to show the status at a high level. So all sites starting construction will be blue, all with engineering completed will be yellow etc.
    My columns are as follows:
    ID,Date,ID&Activity,

    The plan I have used is as follows:
    1. Use =MID(D2,2,FIND(")",D2,1)-2) to duplicate only the ID code from the code+activity cell
    2. Use =VLOOKUP(MAX(C2:C3),C2:D3,2,FALSE) to show the lastest date in the data associated with that ID code (manually selected - this is where I need to try and automate)
    3. Use =IF(ISNUMBER(SEARCH("Start",F2:F3)),"a",IF(ISNUMBER(SEARCH("Engineering",F2:F3)),"b",IF(ISNUMBER(SEARCH("Deliverable",F2:F3)),"c",IF(ISNUMBER(SEARCH("Construction",F2:F3)),"d",IF(ISNUMBER(SEARCH("Land",F2:F3)),"e"))))) to return my letter
    4. Repeat steps above manually for each set of data with the same ID code

    Once again, I hope that makes sense! Thanks for any help!

  6. #6
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Function to return different values based on adjacent cell

    You should create a new thread, but this wouldnt be the first "one more question" thread ever. with that said, could you attach a sample workbook?

  7. #7
    Registered User
    Join Date
    03-14-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Function to return different values based on adjacent cell

    Thank you! In future I'll create a new thread, but if it's ok for this one I'll keep it all in the one so I can copy the info and send it to a colleague when it's solved.

    I have attached the workbook. The dates & activities are fictitious for now, the actual data will be hundreds of lines long.

    Thanks for your help!

  8. #8
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Function to return different values based on adjacent cell

    give this a shot, confirmed by Ctrl+Shift+Enter

    =VLOOKUP(MAX(IF(B2:B21=B2,C2:C21)),C:D,2,0)

  9. #9
    Registered User
    Join Date
    03-14-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Function to return different values based on adjacent cell

    THANK YOU!!!! That is simply amazing. Really appreciate your help!

  10. #10
    Registered User
    Join Date
    03-14-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Function to return different values based on adjacent cell

    One more question... When I try and copy the formula down the rest of the column, it automatically updates to that row number. EG:
    =VLOOKUP(MAX(IF(B11:B351=B11,C11:C351)),C:D,2,0)
    =VLOOKUP(MAX(IF(B12:B352=B12,C12:C352)),C:D,2,0)

    Is there any way to copy the formula to each row and only change the =B$ reference? I started changing it manually, but thats a painful task and one I'd rather not repeat each month.

  11. #11
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Function to return different values based on adjacent cell

    Just need to add $ to the numbers you dont want to change.

    =VLOOKUP(MAX(IF(B$1:B$350=B11,C$1:C$350)),C:D,2,0)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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