Average, Collect are not working when referencing a formula outcome

Hi, I'm running into an issue. I have a column of values [OGS Program Area] that are populated from a vlookup. I need to average the Program Area score based on that output. My formula is below.

=AVG(COLLECT(Score:Score, [OGS Program Area]:[OGS Program Area], "DRE ADMINISTRATION"))

I keep getting the #NO MATCH error. I feel like my error stems from a problem where my AVG formula cannot read the end-state vlookup value. Any workaround I have tried does not work. If I manually create a column and type in the cell DRE ADMINISTRATION, then the calculation works seamlessly. I do not want to have to manually type thousands of these values each month.

Can anyone help with this issue?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!