If cell matches string criteria in one column, THEN return value of another column (same row)

arnufelix

Hello all,

I have the following problem. In my first Smartsheet under the column "Revised Catalog Release" I want to output a date value if a criterion is meet.

Now this is the other sheet I am referencing:

In lay man words what I want is: If column "Task Name" = "Packaged Part Catalog Release" then please return the value of cell "End Date" in this case 02/10/23 else return "0".

I tried =IF(MATCH("Packaged Part Catalog Release", {EL-AMP-11 Schedule Range 1}, 0), INDEX({EL-AMP-11 Schedule Range 6}, 3), 0)

and/or

=IF(({EL-AMP-11 Schedule Range 5} = "Packaged Part Catalog Release"), INDEX({EL-AMP-11 Schedule Range 6}, 2), 0)

to no avail. "EL-AMP-11 is the name of the second Smartsheet.

Could you please help me?

Thank you in advance.

DHarris

Something I have learned recently from this site and may help you as well with your thoughts, but the pro's may jump in and answer just as well:

=INDEX({FirstSmartSheet Range 5}, MATCH([Item #]@row, {FirstSmartSheet Range 6}, 0))

INDEX / MATCH may be what you are looking for. Again, I am not a pro, but have been learning alot within this community and used this within one of my sheets to complete something VERY similar to what you are trying to do.

Place this in the row/column you wish to have your data populate in.

Hope this may help you.

Ray Lindstrom

Hi @arnufelix,

On your first grid, you need to create 2 Cross Sheet References. Do this by right-clicking on any cell, and select Manage References.

The first reference is for the Task Name of the second grid. Name that something like "TaskName", and select the column header for the Task Name column.

The second reference is for the End Date. Name that something like "EndDate", and select the column header for the End Date column.

Then your formula will look like this:

=IF((CONTAINS("Packaged Part Catalog Release", {TaskName})), (INDEX({DueDate}, MATCH("Packaged Part Catalog Release", {TaskName}))), 0)

However because the column you're applying the formula to is a date column, and the "else" is a 0, the 0 won't display. You can turn the 0 into a string, so it will display, by using this:

=IF((CONTAINS("Packaged Part Catalog Release", {TaskName})), (INDEX({DueDate}, MATCH("Packaged Part Catalog Release", {TaskName}))), "0")

arnufelix

@Ray Lindstrom

Thank you for your answer Ray. Since I don't have a full license and my company is on break for the holidays (before I can get access to one as these things take time with IT as you know), I tried the following formula referencing the whole columns instead of TASK NAME or END DATE as you mentioned above.

This is what I got:

=IF((CONTAINS("Packaged Part Catalog Release", {EL-AMP-11 Schedule Range 1})), (INDEX({EL-AMP-11 Schedule Range 5}, MATCH("Packaged Part Catalog Release", {EL-AMP-11 Schedule Range 1}))), "0")

it throws me a "NO MATCH" which means the formula is working but for some reason is not finding the string "Packaged Part Catalog Release". Now I tried with different strings, for instance the string "Project Start" being literally the first row of my TASK NAME column and it worked, and it threw me the END DATE on that row. Maybe the formula is only reading the first row?

=IF((CONTAINS("Project Start", {EL-AMP-11 Schedule Range 1})), (INDEX({EL-AMP-11 Schedule Range 5}, MATCH("Project Start", {EL-AMP-11 Schedule Range 1}))), "0")

Please advise and thank you. Happy Holidays.

Arnu

Paul Newcome

Try double clicking into the cell on the referenced sheet that contains "Packaged Part Catalog Release". Smartsheet hides extra spaces when displayed but keeps them on the back end.

A space space space space B

will look like

A space B

but will only match on

A space space space space B

arnufelix

Thank you @Paul Newcome but that didn't work either unfortunately.

Paul Newcome

Have you tried using a 0 (zero) in the last portion of the MATCH function?