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

Ed Close

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

sean59916

Hi -

When defining the ranges ({Status Report Repository Scope Range}, {Project Name Range}) did you select at the column level or row level?

Sean

Ed Close

Column level.

Mike L.

Try reducing the formula to just the match statement to see if you can get the correct return value. Maybe experiment with the search type value to see if that helps. 

 

MATCH(search_value, range, [search_type]

search_type—[optional] 

The default is 1. The manner in which to search, depending on whether the range is sorted ascending (1), not sorted (0), or sorted descending (-1).

Paul Newcome

I have found that I personally get the most accurate results using 0.

Ibrahim Khaleel

Thanks for nice solution