Returning a value based on parsing info in another Smartsheet
I am trying to return a value from another smartsheet based on information from my current smartsheet...
I have a WBS number in my current sheet (Sheet A) that I am using as a reference point (ex. 1LO150001234P150109), and I am looking for the first 13 characters of that WBS number in a specific column of a different sheet (Sheet B) (ex. 1LO150001234). If it finds a match to that number from Sheet A in Sheet B, I want it to return a value from a different column in that same row of Sheet B to a cell in Sheet A (ex. return the value of either a red, yellow, or green dot).
Add'l Info... It is likely that there will be multiple instances of finding a match on Sheet B, and that the corresponding values can be different (ex. three rows that match "1LO150001234" and one is a green dot, one is a yellow dot and one is a red dot. If that happens I only want it to return the value of the worst case (ex. red if it exists anywhere, yellow if it exists anywhere, green if it exists anywhere, or blank if its blank).
Here is where I am right now with the formula, but I can't think of how to complete it... any help would be appreciated.
=IF(LEFT([Project Number]2, 13) = {ORBLE Range 1}, LARGE({ORBLE Range 2}, 1), "???")
Best Answers

You are going to need to use a COUNTIFS to count how many are red, how many are yellow and how many are green. The LARGE function only works on numbers because strings and symbols could have different meanings for different people.
=COUNTIFS({Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {Range 2}, @cell = "Red")
Then you would nest that COUNTIFS into a nested IF to say that if the count of red is greater than zero, output red, if the count for yellow is greater than zero, output zero, so on and so forth.
=IF(COUNTIFS(..............., "Red")>0, "Red", IF(COUNTIFS(..............., "Yellow")>0, "Yellow", IF(COUNTIFS(..............., "Green")>0, "Green")))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Not quite. There should only be 1 COUNTIFS inside of each IF.
I have
IF(COUNTIFS(......., "Red")>0
but you have
IF(COUNTIFS(=COUNTIFS(.........., "Red"), "Red")>0
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Answers

You are going to need to use a COUNTIFS to count how many are red, how many are yellow and how many are green. The LARGE function only works on numbers because strings and symbols could have different meanings for different people.
=COUNTIFS({Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {Range 2}, @cell = "Red")
Then you would nest that COUNTIFS into a nested IF to say that if the count of red is greater than zero, output red, if the count for yellow is greater than zero, output zero, so on and so forth.
=IF(COUNTIFS(..............., "Red")>0, "Red", IF(COUNTIFS(..............., "Yellow")>0, "Yellow", IF(COUNTIFS(..............., "Green")>0, "Green")))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

@Paul Newcome That is very helpful... here is where I am at now:
=IF(AND(LEFT([Project Number]@row, 13) = {ORBLE Range 1}, COUNTIFS({ORBLE Range 2}, "Red") > 0, "Red", IF(COUNTIFS({ORBLE Range 2}, "Yellow") > 0, "Yellow", IF(COUNTIFS({ORBLE Range 2}, "Green") > 0, "Green"))))
I am getting "Incorrect Argument Set".
Essentially, I think I am asking it to look at the first 13 characters of a cell on Sheet A ("Project Number"@row) and compare them the all the cells in a specific column on Sheet B ("ORBLE Range 1"). If those first 13 characters match any cell in that column of Sheet B, then add up the colored ball symbols from a different column of Sheet B (ORBLE Range 2). If there is more than 0 red ball symbols, then return a red ball, if not, then look for any yellow ball symbols and if there are more than 0, return a yellow ball... so on and so forth for the greens.
Is my nested formula in the right ballpark for this?

No. Each of your COUNTIFS would be the same syntax I had in my first formula. Then you would create the nested IF using the CLUNTIFS the same way I have in my second formula.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

One more try... with my brain turned on this time...
=IF(COUNTIFS(=COUNTIFS({ORBLE Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {ORBLE Range 2}, @cell = "Red"), "Red") > 0, "Red", IF(COUNTIFS(=COUNTIFS({ORBLE Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {ORBLE Range 2}, @cell = "Yellow"), "Yellow") > 0, "Yellow", IF(COUNTIFS(=COUNTIFS({ORBLE Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {ORBLE Range 2}, @cell = "Green"), "Green") > 0, "Green")))
Still getting an INVALID OPERATION error.

I think I just needed to get rid of the extra = sign... tell me if I'm wrong.

Got rid of the extra = sign, but now all the cells are showing blank... not red, yellow, or green dots.

Not quite. There should only be 1 COUNTIFS inside of each IF.
I have
IF(COUNTIFS(......., "Red")>0
but you have
IF(COUNTIFS(=COUNTIFS(.........., "Red"), "Red")>0
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Finally got it right!
=IF(COUNTIFS({ORBLE Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {ORBLE Range 2}, @cell = "Red") > 0, "Red", IF(COUNTIFS({ORBLE Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {ORBLE Range 2}, @cell = "Yellow") > 0, "Yellow", IF(COUNTIFS({ORBLE Range 1}, CONTAINS(LEFT([Project Number]@row, 13), @cell), {ORBLE Range 2}, @cell = "Green") > 0, "Green")))
Huge thanks to @Paul Newcome !!!

Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!