#IF INDEX/MATCH Returning #INVALID OPERATION

KevinBradford

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!

isabonita

@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...

KevinBradford

Hilarious, that fixed it! I figured it would be something simple..... Thank you so much, isabonita

isabonita

@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...

KevinBradford

Hilarious, that fixed it! I figured it would be something simple..... Thank you so much, isabonita