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
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
-
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.
-
Hi Brian,
Thank you for your response. Unfortunately I got an 'Unparseable error'
-
=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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives