SubIndex

SubIndex(a, u, i)

Assuming array «a» has index «i», it returns the value of «i» for which «a» [«i»] = «u». If there is no match for «u» in «a», it returns Null. If there are multiple matches, it returns the last value of «i». If «a» has index(es) in addition to «i», or if «u» is an array with other indexes, those indexes also appear in the result.

Subindex is similar to Vlookup and Hlookup in Excel.

SubIndex is the inverse of SubScript: Where SubScript maps from the index value to the array value, SubIndex maps from the array value to the index value. SubIndex is not as efficient as SubScript -- SubScript uses a hash-table for constant-time associative lookup, but SubIndex must search the array for the indicated value, which takes time linear in the size of the array.

Example

Let:

Variable A :=

I ▶
'a'	'b'	'c'	'd'	'e'	'f'
4	3	2	5	3	1

Then:

SubIndex(A, 2, I) → 'c'

SubIndex(A, 3, I) → 'e'

SubIndex(A, 6, I) → «null»

SubIndex(A, 1..5, I) → ['f', 'c', 'e', 'a', 'd']

Details & More Examples

Array abstraction

When «a» has more than one dimension, or «u» is an array, SubIndex array abstracts according to the standard rules of array abstraction. For example, when «a» contains an index other than «i», SubIndex is applied to each slice of that index, returning the position of «u» in the corresponding slice. When «u» contains an array of values, the position of each of those values in «a» is returned.

Example 1

Define variable A as above and let:

Variable B :=

	I ▶
J ▼	'a'	'b'	'c'	'd'	'e'	'f'
1	3	5	2	4	5	3
2	2	1	5	2	3	4

Variable C :=

K ▶
1	2	3	4	5
4	2	5	1	3

Then:

SubIndex(B, 2, I) →

J ▶
1	2
'c'	'd'

SubIndex(A, C, I) →

K ▶
1	2	3	4	5
'a'	'c'	'd'	'f'	'e'

If «u» is an array of values, in this case the variable C, an array is returned:

SubIndex(B, C, I) →

	K ▶
J ▼	1	2	3	4	5
1	'd'	'c'	'e'	«null»	'f'
2	'f'	'd'	'c'	'd'	'e'

Example 2

The following example can be found in the User Guide Examples.

Let:

Variable Car_prices :=

	Years ▶
Car_type ▼	2005	2006	2007	2008	2009
VW	$16,000	$17,000	$18,000	$19,000	$20,000
Honda	$18,000	$19,000	$20,000	$22,000	$24,000
BMW	$25,000	$26,000	$28,000	$30,000	$32,000

Index Car_type := ['VW', 'Honda', 'BMW']

Then:

SubIndex(Car_prices, 18K, Car_type) →

Years ▶
2005	2006	2007	2008	2009
Honda	«null»	VW	«null»	«null»

SubIndex(Car_prices, 18K, Years) →

Car_type ▶
VW	Honda	BMW
2007	2005	«null»

SubIndex(Car_prices, [18K, 19K] Car_type) →

Years ▶
2005	2006	2007	2008	2009
Honda	«null»	VW	«null»	«null»
«null»	Honda	«null»	VW	«null»

See Also

• PositionInIndex
• Subscript
• Array-reducing functions
• User Guide Examples / Array Function Examples.ana / Reducing Functions Module