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
-
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
-
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:
-
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 -
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!