Difference between INDEX() and ANY() with lists

By and large, you can use INDEX() or ANY() interchangeably.

There is 1 scenario that I know of where a false-positive can be reached when using ANY()

Let’s say you’ve got a Current_User (slice) with a field that users can change to turn on a “special mode” inside your app, and you’ve keyed everything (show ifs for columns, views, actions, etc.) off the presence of a value inside that field.

If you used the following formula:


When this is evaluated, and the list is empty, it can return TRUE.

This happens because the ANY() tells the system to pull out the first item in the list

  • that list being all the records inside the Current_User (slice).

And since there is a record present for the Current_User (slice)…

  • ISNOTBLANK() = true
    • even though the actual value is {nothing}

If you use INDEX() however, this doesn’t happen.

ISNOTBLANK(INDEX(Current_User[User_Special_Mode_Flag], 1))

  • This is because INDEX() tells the system to extract the first value from the list… not just take the first item from the list.
    • this is slightly different from ANY() in the sense that INDEX() both takes the first item, and… um… extracts (??) it too. lol :joy:

So the final result is not the first entry, it’s the first value.

(I hope that made some sort of sense)

Additionally, ANY() has sometimes been known to change data-types from whatever it was (number, decimal, list) to a text.

  • INDEX() has always maintained the correct data-type


However, I was unable to replicate your scenario:



I thought maybe I mis-read, so I changed this, to have a Slice of zero records:


But still:



  • this is one of those wishy-washy bugs, it gets fixed then it breaks then it gets fixed again, but it seems like lately that’s starting to taper off and it’s just always starting to work.

I don’t remember if there’s any more edge cases, the only one I could remember is this is not blank thing.

  • but thankfully it looks like that’s no longer a problem!

If i recall correctly, i have used ANY() where i need to look back at a parent table from a child record in order to change the value of a parent, what you said makes sense now when i look back. So Index is now my goto expression? I would think that after so many years there would be something built in that would do that.