Computes the index at which a specified element is first found within an array. Indexing is left to right.
  • Leftmost index value is 0.
  • If the element is not found, null is returned.
  • For right-to-left searching, use ARRAYRIGHTINDEXOF.
    • If only one element exists in the array, both functions return the same value.
    • For more information, see ARRAYRIGHTINDEXOF Function.

Basic Usage

Array literal reference example:

derive type:single value:ARRAYINDEXOF(["A","B","C","D"],"C")

Output: Computes the index of the element "C" in the array, which is 2 in an 0-based index.

Column reference example:

derive type:single value:ARRAYINDEXOF([myValues],myElement) as:myIndexOfmyValues'

Output: Generates the new myIndexOfmyValues column containing the index in the myValues arrays for the elements listed in the myElement column.


derive type:single value:ARRAYINDEXOF(array_ref,my_element)

ArgumentRequired?Data TypeDescription
array_refYarray or stringName of Array column, Array literal, or function returning an Array to apply to the function
my_elementYanyThe element to locate in the array

For more information on syntax standards, see Language Documentation Syntax Notes.


Name of the array column, array literal, or function returning an array whose element you want to locate.

  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?Data TypeExample Value
YesString (column reference or function) or array literalmyArray1


Element literal that you wish to locate in the array. It can be a value of any data type.

Usage Notes:

Required?Data TypeExample Value


Example - Computing points based on position of finish

This example covers the following functions:

  • ARRAYINDEXOF - Returns the index value of an array for the specified value, searching from left to right. See ARRAYINDEXOF Function.
  • ARRAYRIGHTINDEXOF - Returns the index value of an array for the specified value, searching from right to left. See ARRAYRIGHTINDEXOF Function.


The following set of arrays contain results, in order, of a series of races. From this list, the goal is to generate the score for each racer according to the following scoring matrix.

Did Not Finish (DNF)-20




Note that the number of racers varies with each race, so determining the position of the last racer depends on the number in the event. The number of racers can be captured using the following:

derive type: single value: ARRAYLEN(RaceResults) as: 'countRacers'

Create columns containing the index values for each racer. Below is the example for racer1:

derive type: single value: ARRAYINDEXOF(RaceResults, 'racer1') as: 'arrL-IndexRacer1'

derive type: single value: ARRAYRIGHTINDEXOF(RaceResults, 'racer1') as: 'arrR-IndexRacer1'

You can then compare the values in the two columns to determine if they are the same.

NOTE: If ARRAYINDEXOF and ARRAYRIGHTINDEXOF do not return the same value for the same inputs, then the value is not unique in the array.

Since the points awarded for 1st, 2nd, and 3rd place follow a consistent pattern, you can use the following single statement to compute points for podium finishes for racer1: computing based on the value stored for the left index value:

case condition: ifThenElse if: {arrayL-IndexRacer1} < 3 then: (3 - {arrayL-IndexRacer1}) * 10 else: 0 as: 'ptsRacer1'

The following transform then edits the ptsRacer1 to evaluate for the Did Not Finish (DNF) and last place conditions:

set col: ptsRacer1 value: IF(ISNULL({arrayL-IndexRacer1}), -20, ptsRacer1))

You can use the following to determine if the specified racer was last in the event:

set col: ptsRacer1 value: IF(arrR-IndexRacer1 == countRacers, -10, ptsRacer1)



Was this page helpful? Let us know how we did:

Send feedback about...

Google Cloud Dataprep Documentation
Need help? Visit our support page.