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

Options

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.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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
    Options

    Have you investigated using JOIN with your COLLECT ?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!