Formula to Lookup multiple values in another sheet


I have a problem where I have two sheets, one with current information and one with historical data from 2021-2022. On both sheets, I have a concatenated field which is the unique id i am using to match for a lookup from the current to the historical sheet. the lookup field is a text field.

In the current sheet, if a row's lookup id (concatenated) matches that of any row in the historical, i would like to bring back that ROWID field. The ROWID field is actually a hyperlink back to the row in the historical sheet.

The problem i am having is two-fold.

  1. the historical sheet may have more than one row which matches the lookup value. I would like to bring them all back. i.e. multiple ROWIDs.
  2. the historical sheet ROWID is a hyperlink field, how can I bring back not just the text, but the hyperlinked text?

I have looked through the various posts and tried JOIN/COLLECT formulas which bring back blank values. as well as JOIN/INDEX/COLLECT which results in an #INVALID COLUMN error.

I am at a loss, to even solve #1 above.

As well, we have DataMesh and tried that as well, but that didn't work either.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!