INDEX and MATCH across two sheets: a detailed explanation

Paul Newcome

@Mariann Carmen If you already have the multiple resource managers in a single cell and you want to bring that single cell with multiple RMs over into a single cell, then all you should need to do is make sure both columns are formatted to allow multiple contacts in a single cell.

If you are wanting to JOIN multiple contact cells together into a single multi-contact cell, that is not going to be possible with a formula.

Mariann Carmen

Hi @Paul Newcome!

Yes, I would like to JOIN multiple contact cells together into a single multi-contact cell. I tried the first suggestion, making the columns formatted to allow multiple contacts, but that didn't work.

Then I tried the following formula: =JOIN(COLLECT([Resource Manager]:[Resource Manager], [Assigned To]:[Assigned To], [Assigned To]:[Assigned To] = true), ", ") . This works well when the Assigned To column is a checkbox, and it collects the Resource Managers into one box the way I need. However, in my real scenario, I don't know the Assigned To resources until they are entered into the column (not as a checkbox), and that is what needs to drive the collection of their Resource Manager from the worksheet. In my worksheet, I have one Resource for one Resource Manager (so one Resource Manager could have many Resources assigned to them, all on separate rows in the worksheet).

Any suggestions on how to do this without the checkbox, triggered when the Resource is added?

Paul Newcome

@Mariann Carmen That goes back to my last comment.

If you are wanting to JOIN multiple contact cells together into a single multi-contact cell, that is not going to be possible with a formula.

Mariann Carmen

@Paul Newcome, what formula should I use to accomplish this? I'm stumped.

Paul Newcome

@Mariann Carmen There is no formula that can make this happen.

Mariann Carmen

Ok. Thank you.

Mariann Carmen

@Andrée Starå, No, unfortunately that didn't work. I'm told that there isn't a formula that can accommodate this.

Genevieve P.

Hey @Mariann Carmen

Please feel free to vote on and add your voice to this Product Idea: Combine Contacts from multiple columns into a single cell

Cheers,

Genevieve

daly.166

Hello, I have been following the instructions listed here but can't seem to figure out where I'm going wrong in my formula. I am trying to create a public facing Smartsheet where users can look up a breakdown of their total billing for an event by looking up their event number. I was able to make this work when all of the data was on the same page, but when I tried to build in references to another page, my formula became unparseable.

Here is what the formula looked like when it was all in one sheet and worked:

=INDEX([Total Personnel]:[Total Personnel], MATCH(Search@row, [Event #]:[Event #], 0))

And here is what the unparseable formula looks like:

=INDEX({Total Personnel Reference}:{Total Personnel Reference}, MATCH(Search@row, {Event # Reference}:{Event # Reference}, 0))

I built in the references to my other sheet, but they do not seem to be taking. I have tried different positioning for the brackets, but that does not seem to work either.

If anyone has any suggestions, please let me know!

Paul Newcome

@daly.166 When using cross sheet references, you only enter the reference one.

Same sheet:

[Column Name]:[Column Name]

Cross Sheet:

{Column Name}

daly.166

Thank you, Paul! That worked!

MTodd

@Andrée Starå

I have seen your comment in a few different threads: "Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet."

Can you send me a link for info on this helper sheet method? Does it allow you to pull attachments/comments from the source sheet to the destination sheet?

Paul Newcome

@MTodd It does not allow for attachments or comments to be pulled as formulas cannot grab attachments or comments.

Andrée Starå

Hi @MTodd

I hope you're well and safe!

Unfortunately, having attachments linked using formulas is impossible now, but it's an excellent idea!

Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.

Possible workaround(s)

• There are ways to have the comments collected and shown in another sheet.

Do you still want my method even if you can't have the attachments pulled to the new sheet?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Debbie C.

Can I search for an INDEX match ,with an OR criterion? How would that statement be set up.....struggling a bit with the code: for example

=if(index({Sheet A}, match([SheetBQueryColumnName]@row, {Sheet A}, 0)) = [SheetBQueryColumnName]@row, 1, 0) OR if(index({Sheet A}, match([SheetCQueryColumnName]@row, {Sheet A}, 0)) = [SheetCQueryColumnName]@row, 1, 0)

Is this even allowed?