LSEARCH() alternative: Find the index of a list element in AppSheet

[Joseph_Seddik]

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: 

1. Searched list has to exist in a column or manually constructed using LIST() command, NOT with the {} notation.
   
   
2. Elements do NOT have to be of the same length. 
   
   
3. Search is case sensitive. You can do a case-insensitive search by wrapping both, the pattern and the searched list in AppSheet's UPPER() command. 
   
   
4. The expression performs perfect, not partial, matching. 
   
   
5. The expression returns a Number value which is the index of the element matching the pattern in the searched list, or 0 if no match has been found. 

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
)

[Marc_Dillon]

Can you please describe in plain language how this part works?

FIND(CONCATENATE(" ,, ", [pattern], " ,, "), SUBSTITUTE([list], " , ", " ,, "))
  - FIND(CONCATENATE(" , ", [pattern], " , "), [list]) 
  + 2

[Joseph_Seddik]

Yes sure, for example: 

1. LIST(1, 22, 333, 4444)  
2. converted to text: "1 , 2 , 333 , 4444" 
3. let's search for the 333 element. 
4. the second CONCATENATE constructs the search pattern as: " , 333 , " 
5. the second FIND returns the position: 6 
   
   
6. SUBSTITUTE converts the text in 2. to: "1 ,, 2 ,, 333 ,, 4444" 
7. the first CONCATENATE constructs the search pattern as: " ,, 333 ,, " 
8. the first FIND returns the position: 7 
   
   
9. The arithmetic expression evaluates to: 7 - 6 + 2 = 3, which is the index of the searched element.  

[Marc_Dillon]

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)

[Joseph_Seddik]

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

[Suvrutt_Gurjar]

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

https://www.googlecloudcommunity.com/gc/Tips-Tricks/How-to-find-the-Index-position-of-an-item-from-a...

[Joseph_Seddik]

Thanks @Suvrutt_Gurjar , didn't know about them, sorry, I'll go read 🙂

[Suvrutt_Gurjar]

No problem @Joseph_Seddik . I think it always helps to have more options.