Lookup To Return Multiple Results — Using JOIN, COLLECT
Hello,
I am trying to use JOIN, COLLECT to return multiple results but just can't seem to get it to work.
This is what I have:
=JOIN(COLLECT([Column111]:[Column111], [Column7]:[Column7], [Column1]@row, "N/A"))
[Column111]:[Column111] is the range of the data I want to be returned
[Column7]:[Column7] is the range of where to look for the data
[Column1]@row is the cell that contains the data I want to find within the range
N/A is the result I am looking for if there are no matches
Any ideas?
Thank you!
Best Answer
-
I figured it out! Thanks anyhow!
Answers
-
I think it would be easier to use vlookup
=vlookup(search_value,Lookup_Table, Collumn num, Match_Type)
and if you want to return an NA you can do
an if error
=iferror(vlookup(search_value,Lookup_Table, Collumn num, Match_Type),"N/A")
-
Hi, thank you! But that only brings up one result though. I did an INDEX/MATCH which works but only brings up one result and I need multiple results.
-
I figured it out! Thanks anyhow!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!