INDEX COLLECT - 2 criteria

Jennifer Lindquist

Not sure why this formula returns #INVALID VALUE

=INDEX(COLLECT({SOInfo Qty}, {SOInfo SONo}, SO@row, {SOInfo WTStep}, [WT NO]@row), 1)

I'm trying to return the Qty from the reference sheet, SOInfo, if:

SONo matches SO@row

WTStep matches [WT NO]@row)

I get that all may not match and will use the iferror formula, but none of the rows match.

I'm leaving it off so I can troubleshoot.

Reference sheet:

Source sheet:

Lucas Rayala

Hi @Jennifer Lindquist, for starters, just a quick compliment that you have a nice setup on your sheets and formulas -- I appreciate that you use a naming convention when you create your cross-sheet references, and this all lines up nicely.

I'm noticing that your WT Step and WT No are both preceded by zeroes and one is generated by a formula. Have you successfully used these values for match criteria yet? I think this may be the problem, because the line without an entry for WTNO doesn't generate an error. I think they're actually not the same values, they just look like they are. Create a helper column for these values in both pages by simply reflecting those columns and adding a letter to the WT:

="X"+WTNO@row

="X"+WTStep@row

This should force them to like-quantities with the same internal formats. Use these columns as your match for the WT values.

KPH

I believe the INDEX COLLECT will return an #INVALID VALUE error if such an error exists in any of the cross referenced columns. So if you have rows that do match they will not be returned because of an #INVALID VALUE in another row. Can you check your SOInfo for such errors?

Jennifer Lindquist

There are no errors like that in my reference sheet.

I can see one of the records from my source sheet in my reference sheet:

Source

Reference:

Genevieve P.

Hi @Jennifer Lindquist

It looks like the values in your Sales Order column in the desintation sheet have a leading 0 in front: 0164366, however your values in the SONo Column do not: 164366

This means that the formula can't find a match between the two sheets, which is why you're getting an Invalid Value. You could remove the 0 in your formula if you know the source sheet will never have a leading 0, by using the VALUE function:

=INDEX(COLLECT({SOInfo Qty}, {SOInfo SONo}, VALUE(SO@row)+ "", {SOInfo WTStep}, [WT NO]@row), 1)

Does that work? I added the + "" afterwards to translate the number back to a text number, since I see that it appears on the left side of the cell (indicating it's seen as text).

Cheers,

Genevieve

Genevieve P.

As a side note - are you wanting to Index this value or SUM the value if the SOno is on multiple rows?

Lucas Rayala

Hi @Jennifer Lindquist, for starters, just a quick compliment that you have a nice setup on your sheets and formulas -- I appreciate that you use a naming convention when you create your cross-sheet references, and this all lines up nicely.

I'm noticing that your WT Step and WT No are both preceded by zeroes and one is generated by a formula. Have you successfully used these values for match criteria yet? I think this may be the problem, because the line without an entry for WTNO doesn't generate an error. I think they're actually not the same values, they just look like they are. Create a helper column for these values in both pages by simply reflecting those columns and adding a letter to the WT:

="X"+WTNO@row

="X"+WTStep@row

This should force them to like-quantities with the same internal formats. Use these columns as your match for the WT values.

Jennifer Lindquist

Thank you for all the input!

Adding the X in front worked! Thank you!