How to use INDEX(COLLECT) for two criteria?

Options

I am trying to use INDEX (COLLECT) to match information from two columns to turn out a result in the third column. This is just a demo (actual variables are different) but here is the general format I'm going for, where I want to get the "occasion" result in a separate datasheet based on the information in "clothing type" and "color."

The formula I am currently using is: =INDEX(COLLECT({Occasion}, Color@row, {Color}, Clothing Type@row, {Clothing Type}, 1)), but it is showing UNPARSEABLE. If anyone has any tips it would be so appreciated!

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @elm123

    It looks like you have one of the closing parentheses in the wrong place. The "1" at the end of the formula is for the INDEX function, not the Collect function.

    Try this:

    =INDEX(COLLECT({Occasion}, {Color}, Color@row, {Clothing Type}, [Clothing Type]@row), 1)

    Cheers,

    Genevieve

Answers

  • Sameer K
    Sameer K ✭✭✭✭
    Options

    Two things:

    1. In the COLLECT function you need to have the criterion range first and then the criteria, that is, {Color}, Color@row, {Clothing Type}, [Clothing Type]@row
    2. The parsing error is because you did not enclose the column name in square brackets as the "Clothing Type" column has a space in it. Correct syntax is [Clothing Type]@row
  • elm123
    elm123 ✭✭
    Options

    @Sameer K thank you for the answer! I now have this:


    =INDEX(COLLECT({Occasion}, {Color}, Color@row, {Clothing Type}, [Clothing Type]@row, 1)) and I am getting Incorrect Argument message. How do I fix that?

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @elm123

    It looks like you have one of the closing parentheses in the wrong place. The "1" at the end of the formula is for the INDEX function, not the Collect function.

    Try this:

    =INDEX(COLLECT({Occasion}, {Color}, Color@row, {Clothing Type}, [Clothing Type]@row), 1)

    Cheers,

    Genevieve

  • elm123
    elm123 ✭✭
    Options

    @Genevieve P. , thank you! It works perfectly now :)

  • Kevin7859
    Kevin7859 ✭✭✭✭
    Options

    I'm using the formula and it works for most of my data but there is some where it says no match found when there is data. I'm using to return the contact name using the title and county as criterion.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Kevin7859

    Can you post what formula you're using, as well as a screen capture of the source sheet (but block out sensitive data)?

    Would it be possible that the matching value in the source sheet is slightly different than what you're looking for in the formula?

  • Kevin7859
    Kevin7859 ✭✭✭✭
    Options

    I found the issue was one of the criterion was looking a the incorrect column.

  • SeanVt
    SeanVt ✭✭
    Options

    I'm trying to follow this but getting the #INCORRECT ARGUMENT SET erro

    I have table Codes with "Customer-Job", "JobCode" and "Price"

    I have table Accruals where I choose the customer from a dropdown list, enter the job code and hope to get the price.

    My formula is =INDEX(COLLECT({Codes Range}, {customer}, [Customer-Job]@row, {job}, JobCode@row), 1)

    Looks to be a match to yours but I get that error. Can you tell me what I did wrong?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @SeanVt

    Your structure is correct, which leads me to believe there may be an error in one of the referenced columns. Do you have formulas in any of these ranges?

    • {Codes Range}
    • {customer}
    •  {job}

    The other thing to clarify is what columns you're looking at here. Based on your explanation, it sounds like the range you have titled {Codes Range} should be the Price column.

    You'll also want to ensure those 3 ranges all have the same amount of cells selected (ensure they're all selecting the entire column) and that they're all coming from the same sheet.

    Let us know if any of this helped!

    Cheers,

    Genevieve

  • SeanVt
    SeanVt ✭✭
    Options

    @Genevieve P. that was it! I had Codes Range set to the whole table, not just the price column

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Awesome! Glad you got it sorted 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!