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.
- 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.
Answers
-
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?
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!