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/formulacheckifcellcontainstextstringvalueanothercell)
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 rows1113 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