Index Match Failing
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
=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.