List of csv in a cell must match a range

List of csv in a cell must match a range

I have a sheet with a cell in each row that contains a list of comma separated values. I want to evaluate this list against a a different sheet (sheet 2) and return true if the cell contents matches one of the columns of values in sheet 2. I don't even know where to begin.

ie, check that if of these values ==>

matches all the values of one of these columns ==>

and returns the name of the matching column.

Best Answer

Answers

  • 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

  • "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.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @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!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @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

    A

    C

    D

    B


    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.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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.

  • @Paul Newcome , @Genevieve P - amazing! thank you both so much. Absolute genius!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️


    It took me a while to get the hang of an INDEX/MATCH too. Then one day it just kind of clicked.

Sign In or Register to comment.