# If cell contains value from another then populate with another Cell

✭✭

Hi,

Hoping someone can help with this scenario.

2 Sheets (A and B)

If Cell 1 (sheet A) contains same value as Cell 1 (Sheet B), insert contents of Cell 2 (Sheet B) into Cell 2 (Sheet A).

• ✭✭✭✭✭✭

In that case, what you're going to want to do is use JOIN and COLLECT instead of INDEX/MATCH.

=JOIN(COLLECT({Column 2 Sheet B}, {Column 1 Sheet B}, [Column 1 Sheet A]@row), ", ")

In English, Collect all the values from Column 2 Sheet B where the value in Column 1 Sheet B is the same as the value in Column 1 Sheet A on this row, then join them together with a comma and space between each one.

This results in this:

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

Hi @Mike Y. ,

Are Cell 1 and Cell 2 in Sheet B in the same row, just different columns?

• ✭✭✭✭✭✭

In Cell 2 Sheet A, try the following:

=INDEX({Column 2 Sheet B}, MATCH([Column 1]@row, {Column 1 Sheet B}, 0)

{Column 2 Sheet B} is a reference the the entire column in sheet B. When you start your INDEX( formula, the system will show you a hyperlink to "Reference Another Sheet." Click on that, select your Sheet B, and select the entire column containing the value you're looking for.

{Column 1 Sheet B} likewise is a reference to the column in Sheet B containing the Cell 1 Sheet B value you are matching with the value in Column 1 Sheet A.

If this works, and you want to continue it for all rows in your Sheet A, right click on the formula and select "Convert to Column Formula" and it will apply to all rows in the sheet.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭

Thank you Jeff! This formula worked.

However, there is an additional caveat that Column 1 Sheet B has duplicates with corresponding different values in Column 2 Sheet B. I want to account for the different values in Column 2 Sheet B where all the duplicates in Column 1 Sheet B matches with the value in Column 1 Sheet A (has no duplicates).

• ✭✭✭✭✭✭

In that case, what you're going to want to do is use JOIN and COLLECT instead of INDEX/MATCH.

=JOIN(COLLECT({Column 2 Sheet B}, {Column 1 Sheet B}, [Column 1 Sheet A]@row), ", ")

In English, Collect all the values from Column 2 Sheet B where the value in Column 1 Sheet B is the same as the value in Column 1 Sheet A on this row, then join them together with a comma and space between each one.

This results in this:

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭

This worked perfectly! Thank you so much @Jeff Reisman !

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!