Looking for formula to look for partial match in other columns, returning contents of a column
I am hoping to to get some help with a formula that would look for a partial match in one column when compared with another, returning the contents of a different column in the same row.
Column 1 name: Current Process stage
Column 2 name: Current process Stage status
Column 3 name: S1 Conduct Deep Dive Status
Current Process Stage a row will contain a name that matches the first two characters of the first two characters of S1 Conduct Deep Dive Status. In this example, a row in Current Process Stage contains "S1 Conduct Deep Dive". I trying to get a partial match on "S1" using the contents of a row in Current Process Stage and the column name "S1 Conduct Deep Dive Status", copying the contents of the row from S1 Conduct Deep Dive Status into same row of Current Process Stage status.
Before formula:
Current Process Stage Current Process Stage Status S1 Conduct Deep Dive Status
S1 Conduct Deep Dive Completed
S1 Conduct Deep Dive In Process
After Formula:
Current Process Stage Current Process Stage Status S1 Conduct Deep Dive Status
S1 Conduct Deep Dive Completed Completed
S1 Conduct Deep Dive In process In Process
I'm guessing that this needs a combo of Index and Match, but when I have run that it seems that the system wants to match the entire contents, not just the first few characters of the name or contents.
Best Answer
-
The parenthesis in the column names don't need to be escaped out. They can be included with no issues. As for referencing the column names to look for a match in the Stage, it can't be done in Smartsheet. You would need to use a nested IF formula.
=IF([Current Process Stage]@row = "First Stage", [First Stage Status]@row, IF([Current Process Stage]@row = "Second Stage", [Second Stage Status]@row, IF([Current Process Stage]@row = "Third Stage", [Third Stage Status]@row, ……………..
Answers
-
Are you able to provide that same screenshot with the expected outcome manually entered?
-
Absolutely! Appreciate the help, this is has been a big time-drain trying to figure out. Note that I'm bracketing the Stages (S2, S2, S3, etc) in parenthesis, but if that makes for complexity could remove the parenthesis. There are 14 different stages that I'm looking to pair up with a status column. General concept is to show the most current stage along with it's status next to each other so that user doesn't have to pan through 50+ columns to find it.
-
Which column is the formula going into? If it is the column with errors in your latest screenshot, can you provide the screenshot again except with the desired outcome manually entered so we can see exactly what it is you are wanting to automate?
-
Formula would go into Current Process Stage Status. In example that I'm including with the results of the formula manually entered I'm I removed the Parenthesis (S1) from the name headers because I think the parenthesis make the formula more complex due to needing to escape the characters.
-
S1, S2, S3… should be the keys to mapping the Current process Stage rows to the Column name. Formula would pickup the row number from current process stage, then use same row in the column that is matched with S1, S2, etc.
-
The parenthesis in the column names don't need to be escaped out. They can be included with no issues. As for referencing the column names to look for a match in the Stage, it can't be done in Smartsheet. You would need to use a nested IF formula.
=IF([Current Process Stage]@row = "First Stage", [First Stage Status]@row, IF([Current Process Stage]@row = "Second Stage", [Second Stage Status]@row, IF([Current Process Stage]@row = "Third Stage", [Third Stage Status]@row, ……………..
-
Paul, Thanks for taking time to post a response. I will give it a try
-
Works great. I was wrapped around the axle thinking I needed to have an index and match formula, whereas your method is much simpler and workable…
Help Article Resources
Categories
Check out the Formula Handbook template!