How to use INDEX(COLLECT) for two criteria?

elm123

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!

Genevieve P.

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

Sameer K

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

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

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

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

Kevin7859

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.

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

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

SeanVt

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.

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

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

Genevieve P.

Awesome! Glad you got it sorted 🙂