INDEX COLLECT - 2 criteria

Options

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:


Best Answer

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 12/20/23 Answer ✓
    Options

    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.

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    edited 12/18/23
    Options

    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
    Options

    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.
    Genevieve P. Employee Admin
    edited 12/19/23
    Options

    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.
    Genevieve P. Employee Admin
    Options

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

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 12/20/23 Answer ✓
    Options

    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
    Options

    Thank you for all the input!

    Adding the X in front worked! Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!