index and match formula that works in some places and not others

Options
Ed Close
Ed Close ✭✭
edited 12/09/19 in Formulas and Functions

Hi,

I have an index and match formula that works in some places and not others. I tried to attach a screen show that shows what I am trying to do along with this description.

 

My formula in the Time and Attendance row works as expected. The data for overall status, scope, timeline, resources and funding are matched and appear in the summary file.  This formula is what is currently in the scope field in the time and attendance row 98.

=INDEX({Status Report Repository Scope Range}, MATCH([Approved Capability Name]98, {Project Name Range}))

My formula in the other rows is the same (with the exception of the match cells) yet it does not return the expected results. Scope range is the scope column in the source file and project Name Range is the Project Name column.  As you can see, the scope field should say Green for rows 17 and 28 but they come up empty. In row 98 the expected results are shown.

=INDEX({Status Report Repository Scope Range}, MATCH([Approved Capability Name]17, {Project Name Range}))

=INDEX({Status Report Repository Scope Range}, MATCH([Approved Capability Name]28, {Project Name Range}))

I appreciate any guidance, I am somewhat new to Smartsheet and people are looking for me to produce reports out of the platform and I can't figure this out yet.

Thanks,

E

index match screen shot.png

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!