Forum Discussion
One cell has specific word that triggers another cell to show a word
I have a master list of all the projects we are working on at work, and I would like one cell to change based on my input in another cell. I manually change the “Primary Status” in Column P using data validation based off a list. And I would like the “Project Stage” in column O to change based off my input in column P. Below I have shown the master list I cropped to show pertinent info as well as the two listed tables I added to the Name Manager. I have looked everywhere and the closest I got was using this formula: { =INDEX(StageCode,MATCH(TRUE,ISNUMBER(SEARCH(ProjectCode,P5)),0)) }
This somewhat worked, but I am getting wrong responses. You can see issues in rows 7,9,14,15. This was the closest I have been able to get, but I would LOVE some help finishing this. I appreciate any advice yall can give me, thank you!
In O5:
If you have Microsoft 365 or Office 2021:
=XLOOKUP(P5,ProjectCode,StageCode,"-")
If you have an older version:
=IFERROR(INDEX(StageCode,MATCH(P5,ProjectCode,0)),"-")
Fill down.
4 Replies
=INDEX(StageCode,MATCH(TRUE,ISNUMBER(SEARCH(P5,ProjectCode)),0))I switched the search criteria to " P5,ProjectCode " and it works in my spreadsheet. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
- Ah, yes, this also works out wonderfully! I appreciate the fast response and solution to my convoluted formula. I slightly liked the other answer a tad more, just for the shorter formula and that a blank will return a “-”, but your formula worked perfectly for what I was asking as well. Thanks again. You rock!
In O5:
If you have Microsoft 365 or Office 2021:
=XLOOKUP(P5,ProjectCode,StageCode,"-")
If you have an older version:
=IFERROR(INDEX(StageCode,MATCH(P5,ProjectCode,0)),"-")
Fill down.
- The xlookup function worked wonders, thank you so much! I appreciate the quick reply and the clear instruction, you rock.
