Lookup Functions
lookup(z, A, B, [modifier | f])—Looks in a vector or matrix A for a given value z, subject to the conditions of the optionalmodifier, and returns the value(s) in the same position(s) (that is, with the same row and column numbers) in matrix B. When the value(s) are returned, they appear in a vector in column-wise order, starting with the top left corner of B and sweeping down.
hlookup(z, A, r, [modifier | f])—Looks in the first row of matrix A for a given value z, subject to the conditions of the optionalmodifier, and returns the value(s) in the same column(s) in the row specified by r. When the value(s) are returned, they appear in a vector, starting from the left.
vlookup(z, A, c, [modifier | f])—Looks in the first column of matrix A for a given value z, subject to the conditions of the optionalmodifier, and returns the value(s) in the same row(s) in the column specified by c. When the value(s) are returned, they appear in a vector, starting from the top.
vhlookup(z1, z2, A, [modifier | f])—Looks in the first column of matrix A for a given value z1 and in the first row of A for a given value z2, subject to the conditions of the optionalmodifier, and returns the value(s) at the intersection.
match(z, A, [modifier | f])—Looks in a vector or matrix A for a given value z, subject to the conditions of the optionalmodifier, and returns the index (indices) of each matching value. When the value(s) are returned, they appear in a nested array in column-wise order, starting with the top left corner of A and sweeping down.
Arguments
A, B are real, complex numbers or strings, m x n matrices or vectors.
The row and column indices of array elements are numbered starting with zero unless you change the ORIGIN.
r, c are integers:
ORIGINrORIGIN + (m − 1)
ORIGINcORIGIN + (n − 1)
z is real or complex number, or string.
If you specify “range” as the comparison criterion, then z is a two-element column vector.
z1, z2: Either of the two can be a string, but not both. If either is a string, an exact match is made in the corresponding row/column of A.
modifier (optional) is a string that has a default value of “eq” but can have one of the following other string values:
Comparison Rule
Meaning
“eq”
(Default) Matches everything that is equal to scalar or string z.
“near”
Matches everything that is closest to scalar z for a given TOL.
“gt”
Matches everything that is greater than scalar z.
“lt”
Matches everything that is less than scalar z.
“geq”
Matches everything that is greater than or equal to scalar z.
“leq”
Matches everything that is less than or equal to scalar z.
“not”
Matches everything that is not equal to scalar or string z.
“range”
Matches everything that is in the given range specified in a two-element vector z.
f
Matches everything that meets the conditions set by this user-defined comparison function.
f (optional) is a user-defined function of two arguments. Returns either a 0 for false, or any other number for true.
To match all values within 10% of z, create the function f(a,b):=|b-a| < 10% z.
Additional Information
If matrix A contains mixed units, a match is found only when the units are of the same type. Therefore, the first check matches the unit type, and the second check matches the magnitude.
The degree of precision to which the comparisons adhere is determined by the TOL setting of the worksheet. Reducing this value makes the match more strict; increasing it makes the match less strict.
Was this helpful?