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).
Thanks in advance.
Best Answer
-
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
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
-
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
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
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
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
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
Categories
Check out the Formula Handbook template!
