Return an Index position from a list - Workaround available

[BOTFORCE]

Hi all,

I have a List of values in a virtual column:
0.18 , 0.28 , 0.34 , 0.36 , 0.37 , 0.8 , 0.86 , 0.89 , 0.97 , 1.24 , 1.92 , 3.18 , 3.87

I want to do a lookup of values and want to extract the position of this value in this list.
For example: If I do a lookup in the list of the value 0,34 I need the value 3 in return (as 0,34 is the 3rd. position in the List).

This is obviously not possible with standard column expressions. Are there any possible workarounds to get this managed?

Thank you and br.
Robert

[Suvrutt_Gurjar]

You may wish to take a look at the following post thread.

Senior community colleagues like @tsuji_koichi and @Aleksi have shared their insights.

INDEXOF() With Expression Tips & Tricks 💡

There was a recent question post related to finding the position of an element in a list and also a useful feature request for the INDEXOF() function that I upvoted. Till such a function is available, have tried to come up with the following expression. I have tested the expression on an enumlist by entering element to search for in a text as well as text type enum column in the same record. I believe it can be further tweaked / optimized as necessary based on individual use case. Hope it he…

[BOTFORCE]

Hi Suvrutt,

when I use your Expression i get the following error:

[Suvrutt_Gurjar]

I quickly did a test and it is perfectly working for me.

Please ensure the [Pz_Kinn] is a text type column and [Priority] is a list or enumlist type of column.

The expression showing correct for IndexOf column. The expression is searching for “Cherry” in [AllProduct] column a VC of list type.

Result in the app

[MultiTech]

A complicated little bit of formula magic, but something that I’ve shared in the expression library at AppSheetTraining.com

AppSheet Training

AppSheet Expression Library

Search for AppSheet expressions that may solve a particular problem, put your own expressions on display, or get inspired by other’s creations

Here’s the formula:

count(
 split(
   left(
 concatenate([Walkthrough_Link].[Walkthrough_Elements]),
 find(
   [ElementID],
concatenate([Walkthrough_Link].[Walkthrough_Elements])
 ) + 7
),
 " , ") - list("")
)

Here’s the video I made explaining it:

[BOTFORCE]

Hello,

thank you for your reply, whe I use your expression I get the following error:

[BOTFORCE]

[Priority] is the List with values
PZ-Kinn = the column with value to lookup for in the List

[MultiTech · Accepted solution]

inside the LEN(), wrap your column with CONCATENATE() - that should convert it to the correct type.

• You might want to do that in the FIND() as well.

[MultiTech]

Here’s a link to a general post about this topic:

How to find the Index() position of an item from a list Tips & Tricks 💡

Sometimes when working with lists, you find yourself needing to know at what position an item sits in a list. Is it the first thing? The last thing? At what point does this item sit in a list? Is it number 5 in the list? Number 25? I’ve put together a sample app that demonstrates how to find such things: The real magic is with the following formula (which I’ve cleaned so it’s “universal”) count( split( left( concatenate(ENTER_LIST_HERE), find( WHAT_TO_FIND, concatenate(ENTER…

[shaaland]

@Arthur_Rallu - FYI