Formula to check if cell contains a value from a range of cells

MetroBOS

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.

Find more posts tagged with

Formulas

Comments

Schiff A.

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

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

MetroBOS

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

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

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

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

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

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

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