INDEX/MATCH and INDEX/COLLECT Issues

Ariana Arden

Hi, I am essentially trying to combine the two below formulas into one. I keep seeing that I should be able to use a COLLECT formula instead of MATCH to have two criteria, but whatever I do it isn't working.

The formulas:

=IF(WFX@row <> INDEX(WFX:WFX, MATCH(Quarter@row, Quarter:Quarter, 0)), "Yes", "Applied")

=IF(WFX@row = INDEX(WFX:WFX, MATCH([Fabric 1- Code]@row, [Fabric 1- Code]:[Fabric 1- Code], 0)), "Yes", "Applied")

Help please!

Genevieve P.

Hey @Ariana Arden

Can you explain a little more what you're looking to do? Since your Match function is looking for the value in the current row within the same column, I expect this will almost always return the current row's number... meaning your WFX@row will almost always = your Index of the column.

Are you looking to see how many times the specific WFX value appears in the sheet with the current Quarter and the current Fabric 1 Code?

Ariana Arden

I'm sort of trying to identify the uniqueness of the Fabric Code?

There are multiple WFX lines that have the same Fabric Code. The first time in the sheet that a Fabric Code appears, I want the output to be "Yes," and all other times it should be "Applied."

However, I need it to now also be by Quarter. So the first time that the Fabric Code appears within a quarter, it should say "Yes," and all other times "Applied."

Does that make sense?

Genevieve P.

Thanks for the information, @Ariana Arden!

Do you have anything in the sheet that identifies when a row was added, or which is the "first" in the quarter? For example, a Created Date? Or is it just the first row in order (by row number) you want to pull, regardless of when the row was created?

Ariana Arden

It's more about first row in order, the row creation date doesn't matter so much.

Genevieve P.

Hi @Ariana Arden

Ok so the first thing we need to do is make sure the Row Numbers are displayed in each row. That will allow us to later on use where the row appears as part of our criteria (e.g. the Minimum row number is the first one on the sheet).

To do this, I've added 2 columns:

• System Auto Number column titled "Auto"
• Text/Number column with a column formula titled "Row Number"

The formula in the Row Number column is

=MATCH(Auto@row, Auto:Auto, 0)

Now that we can reference the order of rows, we can add that into our formula:

=IF([Row Number]@row = MIN(COLLECT([Row Number]:[Row Number], Quarter:Quarter, Quarter@row, [Fabric 1- Code]:[Fabric 1- Code], [Fabric 1- Code]@row, WFX:WFX, WFX@row)), "Yes", "Applied")

This says:

Check the Row Number in the current row. See if it's the MINIMUM number (highest in the sheet) with the following criteria:

• Current Quarter listed
• Current Fabric Code listed
• Current WFX listed

If it is the minimum number for all 3 criteria, then say "Yes", otherwise say "Applied".

Keep in mind that if your WFX value is different but you have the same Fabric Code, this will be seen as a unique combination.

Let me know if this worked for you!

Cheers,

Genevieve

Ariana Arden

Hi Genevieve,

This is a great start, and I really appreciate the work you put in. Unfortunately, if my WFX value is different but it has the same Fabric Code as others above it, I need this to not be seen as a unique combo.

I think I might be able to rework this formula into what I need though?

Genevieve P.

Hey @Ariana Arden

Ok no problem! In that case you just remove out the WFX value completely, so it's not included as criteria:

=IF([Row Number]@row = MIN(COLLECT([Row Number]:[Row Number], Quarter:Quarter, Quarter@row, [Fabric 1- Code]:[Fabric 1- Code], [Fabric 1- Code]@row, WFX:WFX, WFX@row)), "Yes", "Applied")

so:

=IF([Row Number]@row = MIN(COLLECT([Row Number]:[Row Number], Quarter:Quarter, Quarter@row, [Fabric 1- Code]:[Fabric 1- Code], [Fabric 1- Code]@row)), "Yes", "Applied")

Ariana Arden

This one worked great! Thanks so much!!