Lookup To Return Multiple Results — Using JOIN, COLLECT

Hammermill21

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!

Find more posts tagged with

Formulas

Accepted answers

Hammermill21

I figured it out! Thanks anyhow!

All comments

Antonio Figueroa

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")

Hammermill21

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.

Hammermill21

I figured it out! Thanks anyhow!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.