How to return a cell from another sheet when the criteria is within a text string in the source?

Dustin R. · 02/24/23

I'm trying to pull the notes from a Request sheet into the Review sheet, but I'm only getting results when the search criteria (Contract #) is the only contract in the cell in the Request sheet.

Ex 1: Line 1 in the source, "123" in the Contract #, has "Urgent" in Notes; the current formula returns the correct notes into the Review sheet on the line for contract 123.

Ex 2: Line 2 in the source, "456" and "789" in the Contract # (separated by line break), has "needed Monday" in Notes; the current formula returns #NO MATCH into the Review sheet on the line for contract 456 and the line for contract 789.

Current formula: =INDEX({Request Notes}, MATCH([Contract]@row, {Request Contract}, 0))

I've dabbled with the FIND, HAS, and CONTAINS functions but I'm new to them and haven't had much luck combining them correctly. I've also tried to find something similar to the SPLIT function to separate the contract numbers in the Request sheet without success.

Jeff Reisman · 02/24/23

@Dustin R.

Try INDEX/COLLECT with CONTAINS. CONTAINS looks for text strings within a cell.

=INDEX(COLLECT({Request Notes}, {Request Contract}, CONTAINS(Contract@row, @cell)), 1)

Dustin R. · 02/24/23

@Jeff Reisman Thank you for the suggestion! This formula now returns correct values when there are multiple contracts in a row, but now returns #INVALID VALUE when there is a single contract in the row. I built mock sheets and included screen shots below.

Request sheet:

Review sheet:

Jeff Reisman · 02/24/23

@Dustin R. Ah - OK. Let's wrap it in CHAR to have Smartsheet treat the 123 like text, then the CONTAINS function will work on it:

=INDEX(COLLECT({Request Notes}, {Request Contract}, CONTAINS(CHAR(Contract@row), @cell)), 1)

Dustin R. · 02/24/23

@Jeff Reisman This returns #INVALID VALUE for all rows, but I think CHAR is the culprit because even by itself it returned the same error:

I can try sharing the demo sheets I created if it would help you to play around in them.

Jeff Reisman · 02/24/23

@Dustin R. OK, this one has to work...

=INDEX(COLLECT({Request Notes}, {Request Contract}, CONTAINS(Contract@row +"", @cell)), 1)

Any number plus "" becomes text.

Dustin R. · 02/24/23

@Jeff Reisman still only returns correct values when there are multiple contracts in a row, but returns #INVALID VALUE when there is a single contract in the row.

Jeff Reisman · 02/24/23

https://community.smartsheet.com/discussion/comment/364375#Comment_364375

Dang, that is frustrating. Your values in Contract are numbers, the values in {Request Contract} are numbers EXCEPT when they are text because there is more than one value in the cell.

Everything misbehaves when there are numbers mixed with text values, too.

As much of a PITA as it is, I think your best bet is make a helper column in each sheet that converts the Contract values into text, and just use the CONTAINS formula on all of them.

Use this in your helper columns:

=IF(ISNUMBER(Contract@row), Contract@row +'', Contract@row)

And change your formula to point at the helper column and helper column range from the other sheet:

=IFERROR(INDEX(COLLECT({Request Notes}, {Request Helper Contract}, CONTAINS(HelperContract@row +"", @cell)), 1), "")

How to return a cell from another sheet when the criteria is within a text string in the source?

Answers

Help Article Resources

Categories