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

Options
MetroBOS
MetroBOS
edited 12/09/19 in Archived 2017 Posts

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.

Tags:

Comments

  • Schiff A.
    Schiff A. Employee
    Options

    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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Except there is a piece missing, since "test@test.comdoes not match "xxxxx_email address=test@test.com".

    Somewhere you will need to parse "xxxxx_email address=test@test.comfrom 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

  • MetroBOS
    Options

    It is not a fixed length.  Does this mean I need two separate formulas or can this be done with one formula?

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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

  • MetroBOS
    MetroBOS
    edited 11/08/17
    Options

    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)

  • Pat Lockwood
    Options

    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.

  • MetroBOS
    Options

    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?

  • Pat Lockwood
    Options

    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.

     

    Test Sheet.PNG

This discussion has been closed.