Index Match Failing

natasha.lee

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.

 

Brian W

I'm not entirely sure, but I think this might be the formula:

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

Although, the true and false outputs may be backwards.

 

natasha.lee

Hi Brian,

Thank you for your response. Unfortunately I got an 'Unparseable error'

Paul Newcome

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

 

See if this works for you.