List of csv in a cell must match a range

@Genevieve P I saw this post a few days ago and have been wracking my brain trying to figure something out. I finally had an "Ah-ha" moment.

What do you think of the below?

I think we can get away with an IFERROR/INDEX/MATCH after setting up 2 helper rows and making a slight adjustment to the main data.

If you add two rows to the top as helper rows, the first would replicate each of the column names, and the second row would use

=JOIN(COLLECT(CHILDREN(), CHILDREN(), @cell <> ""), ",")

Then you would indent all of the data rows beneath row 2.

From there you would use an INDEX/MATCH where we pull from row 1 based on a match from row 2 and use an IFERROR to replace #NO MATCH for any rows that aren't matched to a column.

All comments

Hi Miriana,

Would you mind clarifying a bit more, and explaining the process you currently have set up? I'm not quite sure I understand what you're looking for.

In the second picture, are you looking to see if all of the contents of one specific row match what's in the cell in your first picture?

Can I also ask where the information from the first picture (the values with the commas) comes from? It looks like it might be from a JOIN( formula.

Hi Genevieve,

Thank you so much for your reply. To answer your questions:

I am looking to see if the contents of one specific column match whats in the cell of the first picture. ie. I want to evaluate the contents of one cell to see if it matches all of the values of one of the columns in the second sheet. Or none. (I can transpose column data set into rows instead if necessary).

The information from the first picture is not the result of a join formula - it is an extract from a different system that has been added to smartsheet.

Thanks

Miriana

@Genevieve P I saw this post a few days ago and have been wracking my brain trying to figure something out. I finally had an "Ah-ha" moment.

What do you think of the below?

I think we can get away with an IFERROR/INDEX/MATCH after setting up 2 helper rows and making a slight adjustment to the main data.

If you add two rows to the top as helper rows, the first would replicate each of the column names, and the second row would use

=JOIN(COLLECT(CHILDREN(), CHILDREN(), @cell <> ""), ",")

Then you would indent all of the data rows beneath row 2.

From there you would use an INDEX/MATCH where we pull from row 1 based on a match from row 2 and use an IFERROR to replace #NO MATCH for any rows that aren't matched to a column.

"Ah-Ha!" @Paul Newcome I agree - that sounds like a good plan.

@Miriana Dounis let us know if you need more clarification on how to set up those two helper rows.

When it comes time to build the INDEX(MATCH, I would suggest that we first start out without using the IFERROR and then once we've confirmed it works we can add that in after.

@Genevieve P I usually do add in the IFERROR last after testing everything else. I have learned that one the hard way. Haha

@Genevieve P and @Paul Newcome - this is great thank you!

It works although it looks like both sets of csv values need to be sorted into alphabetical order first and they are unsorted. That's doable if i do some processing outside of smartsheet and drop the data back in. (Unless I am using the formulas incorrectly).

Thanks again. Very helpful!

@Miriana Dounis

You shouldn't have to sort the data into alphabetical order, but the strings themselves do have to be in the same order.

So if your row data is A,C,D,B

Then the column would need to read

It doesn't necessarily have to be in alphabetical order, just the same order.

@Paul Newcome thank you - that's what I thought. I will order them.

Happy to help! 👍️

Genevieve@Genevieve P's first comment where she mentioned the JOIN is what triggered my "Ah-ha" moment about joining the column data into a parent row in the first place, so thanks @Genevieve P!

Haha team effort 🙂

The Community is great for that!

Agreed! I love the Community. I have learned A LOT from it.

@Paul Newcome - I feel a little silly but I realise I don't really get the Index match functions. I have this and seem to be returning the column number, eg the right answer is in column 7 so I get 7 as the result.

=INDEX(MATCH(Audiences1, {ADMIN-Tagging groups to blueprint names Range 2}, 0), 1)

What am I doing wrong?

thanks in advance

Miriana

Not silly at all! It's taken me ages to get the hang of an Index/Match formula. I have this little helper-explanation saved on a sticky note...

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match in the other sheet}))

Does that help?

No worries. This is just showing you how the MATCH part works. Haha.

=INDEX(range to pull from, row number, column number)

=MATCH(text to compare, range to compare in, type of match)

We are looking across rows, so we can just go ahead and plug a 1 into the row number section of the INDEX function.

=INDEX(range to pull from, 1, column number)

We already know what range we want to pull from for the INDEX function which is the row that duplicates the column name.

=INDEX({Other Sheet Column Name Dup Row}, 1, column number)

We will use the MATCH function to automate the column number since as you have found, the MATCH function produces a numerical value based on where within the range your data was found. I am going to assume that "Audiences1" is the text you want to search for (the string), and we already know that we want to search the parent row in the other sheet that contains the column strings. I also suggest using 0 as the type of match (like you did) because that provides for an exact match. So that gives us a MATCH that looks like this...

=MATCH(Audiences1, {Other Sheet Parent Row}, 0)

Now we drop that MATCH into the column number portion of our INDEX function:

=INDEX({Other Sheet Column Name Dup Row}, 1, MATCH(Audiences1, {Other Sheet Parent Row}, 0))

And give it a whirl. Once we make sure it is working properly, we can then add in our IFERROR to clean up the #NO MATCH error, and you should be well on your way.