Formula to Lookup multiple values in another sheet

Umesh Shah

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.

Umesh Shah

OK so i solved for #1 above and the formula is now working.

how do i solve for #2 and make the text come back with a hyperlink?

Paul Newcome

The JOIN function will always output a text value. There is no way to use a formula to accomplish exactly what you are wanting to do. You would need to leverage the API, possibly the premium add-on Bridge, or maybe a third party app such as Zapier.