Search multiple columns for data and if matched, return a value from another column on another sheet

I have been working on a variety of index/match; index/collect formulas to solve this problem with no luck as of yet:

Spreadsheet 1 - 1 Column has a list of business names, then there are 4 award columns that represent the name of the sport they are sponsoring + the city the sport is in.

Spreadsheet 2 - Has a list of sports from all the possible markets. I've created a helper column that takes name of the award + market. So this column should match (if there is a sponsor) to one of the 4 columns in the previous sheet.

To avoid forgetting to add the sponsor attribution on the nominee list, I need a formula that searches all possible sponsor names, looks at the 4 columns and then if it matches the sport + market name on the other sheet, it inputs the business name on the second sheet.

I've gotten this formula to work, but it only searches one of the 4 award columns (so doesn't pick up other awards the sponsor may be associated with).

I've spent days on this formula, so any help is soooo appreciated.

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @ccosta

The way I would do this is to write one INDEX(COLLECT per-column. This means you'll have 4 separate formulas to bring back the 4 possible values.

In my sheet I would add this formula into a multi-select cell and use CHAR(10) to create a line break, so that if there's more than one brought back you can visually see them as different values.

For example:

=INDEX(COLLECT()) + CHAR(10) + INDEX(COLLECT()) + CHAR(10)... etc

So, a formula like this:

=IFERROR(INDEX(COLLECT({Column 1 to bring back}, {Range 6}, Market@row, {Range 7}, [Award Name and Market Helper]@row), 1), "") + CHAR(10) + IFERROR(INDEX(COLLECT({Column 2 to bring back}, {Range 6}, Market@row, {Range 7}, [Award Name and Market Helper]@row), 1), "") + CHAR(10) + IFERROR(INDEX(COLLECT({Column 3 to bring back}, {Range 6}, Market@row, {Range 7}, [Award Name and Market Helper]@row), 1), "") + CHAR(10) + IFERROR(INDEX(COLLECT({Column 4 to bring back}, {Range 6}, Market@row, {Range 7}, [Award Name and Market Helper]@row), 1), "")

Let me know if that makes sense and will achieve your end goal!

Cheers,

Genevieve

Amy Flores C

Have you investigated using JOIN with your COLLECT ?

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.