If cell contains value from another then populate with another Cell

Mike Y. · 01/21/22

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

Thanks in advance.

Jeff Reisman · 01/21/22

https://community.smartsheet.com/discussion/comment/316985#Comment_316985

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:

Heather Duff · 01/21/22

Hi @Mike Y. ,

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

Jeff Reisman · 01/21/22

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.

Mike Y. · 01/21/22

https://community.smartsheet.com/discussion/comment/316972#Comment_316972

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

Jeff Reisman · 01/21/22

https://community.smartsheet.com/discussion/comment/316985#Comment_316985

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:

Mike Y. · 01/21/22

https://community.smartsheet.com/discussion/comment/317006#Comment_317006

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

If cell contains value from another then populate with another Cell

Best Answer

Answers

Help Article Resources

Categories