Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Formula to check if cell contains a value from a range of cells
For example, the text in column 4 row 2 is "xxxxx_email address=test@test.com"
- The text in column 2 row 11 is "google@google.com"
- The text in column 3 row 11 is "1"
- The text in column 2 row 12 is "test@test.com"
- The text in column 3 row 12 is "2"
- The text in column 2 row 13 is "smartsheet@smartsheet.com"
- The text in column 3 row 13 is "3"
The formula should look at column 4 row 2 and bring back a "2" based on the value in column 3 row 12.
I have this worked out in Excel, but it does not seem to translate properly into Smartsheet.
Comments
-
Hello MetroBOS,
Based on your description, the LOOKUP() function would be best suited to your needs.
Similar to the VLOOKUP function in Excel, Smartsheet's LOOKUP function looks up a value and returns a corresponding value in the same row but from a different column.
Here is an example of how the formula would look: =LOOKUP([Column4]2, [Column2]11:[Column3]13, 2)
For more information, see: https://help.smartsheet.com/function/lookup
Thanks!
Schiff
Smartsheet Support -
Except there is a piece missing, since "test@test.com" does not match "xxxxx_email address=test@test.com".
Somewhere you will need to parse "xxxxx_email address=test@test.com" from column 4 row 2 to get "test@test.com" for your lookup value and then use the LOOKUP table as mentioned by Schiff.
If the text before the equal sign is fixed in length, then this should do that for you
=RIGHT([Col_4]2, LEN(Col_4]2) - 20)
where [Col_4]2 is column 4, row 2.
If the length of the text to the left of the equal sign is not fixed, you'll want to combine this with the FIND() function that I mentioned in your other post today (https://community.smartsheet.com/discussion/formula-check-if-cell-contains-text-string-value-another-cell)
Craig
-
It is not a fixed length. Does this mean I need two separate formulas or can this be done with one formula?
-
It can be done with one.
I'll need to find time to write it out if someone does not get to it first.
Craig
-
If it is not fixed length (the xxxxx part), then the formula becomes
=RIGHT([Col_4]2, LEN([Col_4]2) - FIND("=", [Col_4]2))
will return test@test.com which then gets put into Shiff's LOOKUP example.
Craig
-
Thank you. Where would it be placed within the LOOKUP? I've tried a few different spots, but I keep getting #UNPARSEABLE.
Also, what if there is text after the lookup value?
For example, the text in column 4 row 2 is "xxxxx_email address=test@test.com_xxxxx"
This is what I was using in Excel, but it appears the SEARCH function does not exist in Smartsheet.
=LOOKUP(1,-SEARCH(B$11:B$13,D2),C$11:C$13)
-
Pulling the information together that has been provided in the other posts your formula would be:
=LOOKUP((RIGHT([Column4]4, LEN([Column4]4) - FIND("=", [Column4]4))), [Column2]11:[Column3]13, 2)
This really isn't achieving what you are doing in Excel where you coming at the problem sort of in reverse, using an array of values to search your target string (Column 4 Row 2). This solution only works with the data example you have provided, mainly searching for an email that doesn't have characters appended to it.
The Find function in SmartSheet doesn't allow you to use an array of values as the "search_for" value which would be an approach like you used in Excel. If you know that you are always looking for an email that ends in .com, you could parse out the email address regardless whether there are any characters appended or not. Again, this is very specific to your example.
-
Thank you, Pat. For some reason, the formula is generating a blank result. It doesn't say that the formula is invalid/unparseable, but it doesn't pull in the value it should either - it's completely blank. Any idea why that would happen?
-
I attached an image of my test sheet. I went through the formula that I cut/paste into that post again to make sure nothing was lost and it looks good.
I changed the argument in Column4 Row 4 to look for different email addresses and changed the values in column3 rows11-13 and everything works just fine. Not sure why you are getting blanks which is why I wanted to start with how I have my test data setup.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives