Hello,
In AppSheet there is no command for finding the index of an element in a list. This is a simple expression you can use to achieve the same purpose.
Considerations:
Expression to use:
IFS(
NOT( IN([pattern], [list]) ), 0,
[pattern] = INDEX([list], 1), 1,
[pattern] = INDEX([list], COUNT([list])), COUNT([list]),
TRUE,
FIND(CONCATENATE(" ,, ", [pattern], " ,, "), SUBSTITUTE([list], " , ", " ,, "))
- FIND(CONCATENATE(" , ", [pattern], " , "), [list])
+ 2
)
Can you please describe in plain language how this part works?
FIND(CONCATENATE(" ,, ", [pattern], " ,, "), SUBSTITUTE([list], " , ", " ,, "))
- FIND(CONCATENATE(" , ", [pattern], " , "), [list])
+ 2
Yes sure, for example:
Ahh. Smart.
Allow me to re-describe it another way, maybe will help someone else understand.
Basically, you're finding the position of the pattern in the text representation of the list, but that doesn't tell you which element of the list it is, because the length of elements can vary.
So then you make a small change to the text, such that it adjusts the number of characters between each and every element by the same amount. Then find the pattern again in the new text, and compare the 2 values. Their difference can be adjusted by a constant pattern/formula that will output the element position in the list itself, because the difference will increase linearly as the index increases.
E.X.
If the difference is 1, then it's the 2nd item,
if the difference is 3, then it's the 3rd item,
5= 4th, 7=5th, etc...
(note, the exact number in my example may not exactly match the above, it's just the general idea)
Yes, thanks much @Marc_Dillon for helping me explain the idea.
The underlying principle is to introduce a cumulative character-position shift that would increase with every new element, so the formula would be:
new character position - old character position + constant = element index
Good one @Joseph_Seddik .
There have been more INDEX OF() posts in the past. However, it always helps to have more options and more ways to accomplish a functionality.
https://www.googlecloudcommunity.com/gc/Tips-Tricks/INDEXOF-With-Expression/m-p/360458
Thanks @Suvrutt_Gurjar , didn't know about them, sorry, I'll go read 🙂
No problem @Joseph_Seddik . I think it always helps to have more options.