#### 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
edited 12/09/19

For example, the text in column 4 row 2 is "xxxxx_email address=test@test.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:

• 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)

Thanks!

Schiff

Smartsheet Support

• ✭✭✭✭✭✭
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

• Options

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

• ✭✭✭✭✭✭
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

• ✭✭✭✭✭✭
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

• 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)

• 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.

• 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?

• 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.

This discussion has been closed.