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

ccosta

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.

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 ?