Index Match Failing

Options
natasha.lee
edited 12/09/19 in Smartsheet Basics

I have the following columns

Scenario ID: Unique Identifier

Scenario Occurrence: 0 if Scenario never occurred, 1 if Scenario Occurred. (Uses countif formula)

Scenario Name: Drop Down Field of Scenario Names

Formula:

=IF([Scenario Occurance]39 = 0, RowA39 + [Row ID]39, INDEX([Scenario ID]$1:[Scenario Name]38, MATCH([Scenario Name]39, [Scenario Name]$1:[Scenario Name]38), 1))

Intention: Check if Scenario Occurred before. If yes, Index Match to get Scenario ID of corresponding scenario name.

I know my scenario names are accurate because I restricted to the drop down. Also, my countif formula recognizes the Scenario Names are equal. 

My formula also works for some of the drop down values that are shorter text fields but not the longer text fields.

Other observations: The fields that have no match typically begins with 'Complete'

Any insight will be greatly appreciated.

 

Comments