INDEX COLLECT - 2 criteria

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 ✓

    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

    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?

  • 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
    edited 12/19/23

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

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

    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.

  • 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!