# Function to return different values based on adjacent cell

1. ## 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. ## 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",""))))

3. ## 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. ## 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. ## 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. ## 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. ## 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.

8. ## 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. ## Re: Function to return different values based on adjacent cell

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

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. ## 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)

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

#### 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