Using Collect to pull data from the same sheet

Marissa Pettifer

I usually use Index(collect to reference separate sheets to pull data, but I'm trying to apply the same logic for pulling column values from the same sheet.

I tried this as an Index(collect formula

=INDEX(COLLECT([QTY on Order]:[QTY on Order], [Ref PN 1]:[Ref PN 1], [Part Number]@row),1

& a collect formula on its own, which is what Smartsheet AI recommended

=COLLECT([QTY on Order]:[QTY on Order], [Ref PN 1]:[Ref PN 1], [Part Number]@row)

But both options are pulling Invalid Value error messages.

Marissa Pettifer

I finally got Smartsheet AI to give me an index(match formula to use instead

=INDEX([Total Planned QTY]:[Total Planned QTY], MATCH([Ref PN 1]@row , [Part Number]:[Part Number], 0))

Paul Newcome

Glad you were able to get it working. It looks like the biggest difference between the INDEX/MATCH and INDEX/COLLECT is that your COLLECT is looking for [Part Number]@row within the [Ref PN 1] column, but then your MATCH is looking for [Ref PN 1]@row in the [Part Number column. Basically the range and criteria is flipped around.

MarceHolzhauzen

Hi @Marissa Pettifer

Invalid Value errors are usually due to a column type mismatch.
In your case, it could be something like the column the formula is in, is a date column but the data is numbers or text.

Your Index Collect Formula seems to be correct, bar a closed parenthesis at the end

Marissa Pettifer

@MarceHolzhauzen, It's a text/number column type trying to pull a number

Paul Newcome

What does this do (temporary just for troubleshooting)?

=COUNTIFS([Ref PN 1]:[Ref PN 1], [Part Number]@row)

Marissa Pettifer

I finally got Smartsheet AI to give me an index(match formula to use instead

=INDEX([Total Planned QTY]:[Total Planned QTY], MATCH([Ref PN 1]@row , [Part Number]:[Part Number], 0))

MarceHolzhauzen

@Marissa Pettifer
Happy that you have found a solution.

Just for your own sanity, the difference between an INDEX MATCH combination and an INDEX COLLECT combination is the following:

INDEX MATCH - similar to vlookup where you display a single value (INDEX) where a reference cell matches (MATCH) a single criterium. In your case, display the Total Planned Qty where the Part Number matches PN1. This formula only wants the part numbers to match.

INDEX COLLECT - similar to a multiple if statement (IFS) where you want to display a single value (INDEX) when multiple criterium (COLLECT) is to be met. In this case, display the total planned qty where part number matches and (whatever other criteria you want to add).

For your use case, INDEX MATCH will work seeing that you're just looking at matching the relevant part number and displaying a single value from there.

I hope this explanation makes sense

MarceHolzhauzen

Also, it would be great if you checked out @Paul Newcome's troubleshoot advice to determine the cause of the error for future reference

Paul Newcome

Glad you were able to get it working. It looks like the biggest difference between the INDEX/MATCH and INDEX/COLLECT is that your COLLECT is looking for [Part Number]@row within the [Ref PN 1] column, but then your MATCH is looking for [Ref PN 1]@row in the [Part Number column. Basically the range and criteria is flipped around.