Help with a Formula Error."Invalid Value"

Rachu · 03/26/24

I have created a formula : =IF(ISBLANK(INDEX({Status}, MATCH([PRO ID]@row, {PRO Id}, 0), MATCH([PRO Name]@row, {Project Name}, 0))), INDEX({Submitted Date}, MATCH([PRO ID]@row, {PRO Id}, 0), MATCH([PRO Name]@row, {Project Name}, 0)), ""). This works for me on the first row and i converted this to column formula. But from 2nd row this formula does not work it shows invalid value. Can anyone help me here?

Leela Lodhi · 03/26/24

Hi @Rachu,

Try below formula

=LEFT(JOIN(COLLECT({Submitted Date}, {PR Name}, [Project Name]@row, {ID}, [PRO ID]@row)), 8)

David Jasven · 03/26/24

I recommend checking the following:

Named Ranges Consistency: Ensure that {Status}, {PRO Id}, {Project Name}, and {Submitted Date} named ranges are consistently applied across all rows in your sheet. Inconsistencies in named range definitions can lead to #INVALID VALUE errors.
Reference Validity: Verify that all references within the INDEX and MATCH functions point to valid ranges and that these ranges are appropriately sized and formatted across the sheet.
Formula Evaluation Context: While the formula should theoretically work when converted to a column formula, it's possible that the dynamic evaluation context of @row is not behaving as expected in combination with your named ranges. You might need to review the named ranges to ensure they're capturing the intended cells across the entire sheet.
Data Types and Formats: Check that the data types and formats of the [PRO ID] and [PRO Name] columns match those of the {PRO Id} and {Project Name} named ranges. Data type mismatches can sometimes result in failed matches.

Rachu · 03/26/24

Hi @David Jasven Thank you so much for your response. I checked everything still unable to figure out the issue.I created a simple formula just to get the submitted date from the form responses. The scenarios is same the formula works for 1st row and then for the remaining rows it does not work. Attached few screenshots. It will be great if you can help me here.