One cell has specific word that triggers another cell to show a word

New Contributor

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!



Picture1.png                                 Picture2.png


4 Replies
best response confirmed by cj4darch (New Contributor)


In O5:

If you have Microsoft 365 or Office 2021:


If you have an older version:


Fill down.




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.

The xlookup function worked wonders, thank you so much! I appreciate the quick reply and the clear instruction, you rock.
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!