#IF INDEX/MATCH Returning #INVALID OPERATION
I am fairly new to formulas in Smartsheet, and I am having trouble with the following formula where I am trying to check a Checkbox from another sheet ("Devices Secured") and set a Text/Number value ("Device Security OK") to either "OK" or "Not OK" based on whether the Checkbox is checked or not. This seems fairly straightforward but whatever I try I get #INVALID OPERATION returned when a row is returned. I have tested the INDEX/MATCH functionality and it seems to be finding the correct row in the external sheet. My current formula is :
=IF(INDEX({User Survey Devices Secured}, =MATCH(Email@row, {User Survey Range Email}, 0)) = 1, "OK", "Not OK")
Any help would be appreciated, because nothing I try seems to work. :(
Formula is in the Device Security OK field in this sheet:
Formula:
Range for INDEX on external sheet:
Range for MATCH
Thanks!
Best Answers
-
@KevinBradford Take out the "=" before the MATCH... I think that will fix it? The rest of it looks good.
That's really great being new to formulas! I wasn't new and it took me a bit to REALLY understand the INDEX/MATCH and how it worked... I just followed the examples...
-
Hilarious, that fixed it! I figured it would be something simple..... Thank you so much, isabonita
Answers
-
@KevinBradford Take out the "=" before the MATCH... I think that will fix it? The rest of it looks good.
That's really great being new to formulas! I wasn't new and it took me a bit to REALLY understand the INDEX/MATCH and how it worked... I just followed the examples...
-
Hilarious, that fixed it! I figured it would be something simple..... Thank you so much, isabonita
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!