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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!