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