Syntax: @MATCH(V, R[, T])
V = Value to be matched
R = Range in which to match V
T = Type of match
@MATCH return the relative position of the value V in the range R, according to the rule specified by T:
1 | find largest value in R <= V |
0 or omitted | find first value in R = V |
-1 | find smallest value in R >= V |
V can be a string or a numeric value. If V is a string, T must be 0 or omitted. If V is a string, it may contain the wildcard characters, asterisk (*) and question mark (?), to represent any sequence of characters and any single character respectively.
Examples:
A | B | |
1 | Products | Quantity (Boxes) |
2 | Apple | 32 |
3 | Orange | 50 |
4 | Cherry | 15 |
5 | Banana | 20 |
6 | Cantaloup | 10 |
7 | Strawberry | 25 |
@MATCH(15, B1..B7) = 3
@MATCH(30, B1..B7, 1) = 6
@MATCH(30, B1..B7, -1) = 1
@MATCH(``Orange'', A1..A7) = 2
@MATCH(``Can*'', A1..A7) = 5