=(INDEX(COLLECT Invalid Value Error

NitaP

I am getting an Invalid value error in this formula:

=(INDEX(COLLECT({Zit Zap 102621 Units Needed}, {Zit Zap 102621 Formula}, Formula@row, {Zit Zap 102621 Inv ID}, [RM Inventory ID]@row), 1))

I'm trying to bring in the RM Units Needed from Spreadsheet 2 when the Formula and RM Inventory ID from Spreadsheet 2 matches the Formula and RM Inventory ID on Spreadsheet 1. The error is on Spreadsheet 1.

Any suggestions?

Paul Newcome

The text in the formula column on your target sheet is "Zit Zap" whereas the text in the formula column on the source sheet is "ZitZap" you have a space between one and not the other. This means the COLLECT function is pulling in zero rows, so the 1 in the INDEX function indicating to pull from the first row is causing the error (since there is no "row 1" for the INDEX function to pull from).

NitaP

I'm adding another screenshot to show what's at the top of Spreadsheet 2 in case that's causing a problem.

Paul Newcome

The text in the formula column on your target sheet is "Zit Zap" whereas the text in the formula column on the source sheet is "ZitZap" you have a space between one and not the other. This means the COLLECT function is pulling in zero rows, so the 1 in the INDEX function indicating to pull from the first row is causing the error (since there is no "row 1" for the INDEX function to pull from).

NitaP

You have a keen eye! Thank you!

Paul Newcome

Happy to help. 👍️

I just did that to myself earlier today and spent quite a bit of time and effort unsuccessfully troubleshooting, so it was still a fresh thought to look for that. Hahaha

mdemul

@Paul Newcome Thank you, I just had the same INVALID VALUE result from an INDEX(COLLECT) function, then realized that one of the conditions in the COLLECT was pulling in zero results. This is not easy to track! I solved it with the IFERROR function, which is not an elegant solution but it works.