1package functions23import (4 "strings"56 "src.rest/cat/012/internal/value"7)89func init() {10 define(11 &FuncDef{Name: "VLOOKUP", Args: "search_key, range, index, [is_sorted]", Desc: "Find a key in the first column and return a value from its row", Min: 3, Max: 4,12 eval: func(args []Node, get lookup) Value { return tableLookup(args, get, true) }},13 &FuncDef{Name: "HLOOKUP", Args: "search_key, range, index, [is_sorted]", Desc: "Find a key in the first row and return a value from its column", Min: 3, Max: 4,14 eval: func(args []Node, get lookup) Value { return tableLookup(args, get, false) }},15 &FuncDef{Name: "MATCH", Args: "search_key, range, [search_type]", Desc: "Position of a key in a row or column", Min: 2, Max: 3,16 eval: match},17 &FuncDef{Name: "INDEX", Args: "reference, [row], [column]", Desc: "The value at a row and column of a range; row or column 0 for a whole column or row", Min: 1, Max: 3, arrays: liftPass,18 eval: index, format: inheritFrom(0)},19 &FuncDef{Name: "XLOOKUP", Args: "search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]", Desc: "Find a key and return the matching entry of another range", Min: 3, Max: 6,20 eval: xlookup, format: inheritFrom(2)},21 &FuncDef{Name: "CHOOSE", Args: "index, choice1, [choice2, ...]", Desc: "The choice at a position", Min: 2, Max: -1, arrays: liftPass,22 eval: choose},23 &FuncDef{Name: "ROWS", Args: "range", Desc: "Number of rows in a range", Min: 1, Max: 1,24 eval: func(args []Node, get lookup) Value { return measure(args[0], get, true) }},25 &FuncDef{Name: "COLUMNS", Args: "range", Desc: "Number of columns in a range", Min: 1, Max: 1,26 eval: func(args []Node, get lookup) Value { return measure(args[0], get, false) }},27 )28}2930// Evaluators for the table above, in its order.3132func match(args []Node, get lookup) Value {33 key := eval(args[0], get)34 if key.Kind == value.Error {35 return key36 }37 m := matrixArg(args[1], get)38 kind, err := intArg(args, 2, 1, get)39 switch {40 case err != nil:41 return *err42 case !m.vector():43 return value.ErrNA44 }45 q := lineSeq(m, get, m.cols == 1)46 var i int47 switch {48 case kind == 0:49 i = q.findExact(key, true)50 case kind > 0:51 i = q.findSorted(key, 1)52 default:53 i = q.findSorted(key, -1)54 }55 if i < 0 {56 return value.ErrNA57 }58 return num(float64(i + 1))59}6061func index(args []Node, get lookup) Value {62 m := matrixArg(args[0], get)63 row, err := intArg(args, 1, 0, get)64 if err != nil {65 return *err66 }67 col, err := intArg(args, 2, 0, get)68 if err != nil {69 return *err70 }71 // In a single row, a lone index counts along the row.72 if m.rows == 1 && len(args) == 2 {73 row, col = 1, row74 }75 if row < 0 || col < 0 {76 return value.ErrValue77 }78 if row == 0 && m.rows == 1 {79 row = 180 }81 if col == 0 && m.cols == 1 {82 col = 183 }84 switch {85 case row > m.rows || col > m.cols:86 return value.ErrNum // as Sheets; Excel says #REF!87 case row == 0 || col == 0: // a whole column, row or both: an array88 return get.arrayValue(m.slice(row, col))89 }90 return m.cell(row-1, col-1)91}9293// slice is row (0 for all) and col (0 for all) of m as an array.94func (m matrix) slice(row, col int) *Array {95 r0, c0, rows, cols := row-1, col-1, 1, 196 if row == 0 {97 r0, rows = 0, m.rows98 }99 if col == 0 {100 c0, cols = 0, m.cols101 }102 a := &Array{Rows: rows, Cols: cols, Fill: m.blank,103 DRows: min(rows, max(m.dataRows-r0, 0)), DCols: min(cols, max(m.dataCols-c0, 0))}104 if tooBig(a.DRows, a.DCols) {105 return &Array{Rows: 1, Cols: 1, Fill: value.ErrValue}106 }107 a.V = make([]Value, a.DRows*a.DCols)108 for r := range a.DRows {109 for c := range a.DCols {110 a.Set(r, c, m.cell(r0+r, c0+c))111 }112 }113 return a114}115116// measure is ROWS, or COLUMNS when rows is false. An error in place of117// a range or array is the answer: ROWS(SEQUENCE(0)) is #NUM!.118func measure(n Node, get lookup, rows bool) Value {119 m := matrixArg(n, get)120 switch {121 case !m.ref && m.rows == 1 && m.cols == 1 && m.blank.Kind == value.Error:122 return m.blank123 case rows:124 return num(float64(m.rows))125 }126 return num(float64(m.cols))127}128129func choose(args []Node, get lookup) Value {130 i, err := intArg(args, 0, 0, get)131 if err != nil {132 return *err133 }134 if i < 1 || i >= len(args) {135 return value.ErrNum // as Sheets; Excel says #VALUE!136 }137 return eval(args[i], get)138}139140// tableLookup is VLOOKUP (vertical) and HLOOKUP: search the first column141// (or row), then return the index-th entry across.142func tableLookup(args []Node, get lookup, vertical bool) Value {143 key := eval(args[0], get)144 if key.Kind == value.Error {145 return key146 }147 m := matrixArg(args[1], get)148 idx, err := intArg(args, 2, 0, get)149 if err != nil {150 return *err151 }152 sorted, err := boolArg(args, 3, true, get)153 if err != nil {154 return *err155 }156 q := lineSeq(m, get, vertical)157 across := m.cols158 if !vertical {159 across = m.rows160 }161 switch {162 case idx < 1:163 return value.ErrValue164 case idx > across:165 return value.ErrRef166 }167 var i int168 if sorted {169 i = q.findSorted(key, 1)170 } else {171 i = q.findExact(key, true)172 }173 if i < 0 {174 return value.ErrNA175 }176 if vertical {177 return m.cell(i, idx-1)178 }179 return m.cell(idx-1, i)180}181182// lookupEqual reports whether a lookup key matches v: same kind, text183// case-insensitive, with wildcards when allowed.184func lookupEqual(key, v Value, wild bool) bool {185 if key.Kind != v.Kind {186 return false187 }188 if key.Kind == value.Text {189 if wild && hasWildcards(key.Str) {190 return wildMatch(key.Str, v.Str)191 }192 return strings.EqualFold(key.Str, v.Str)193 }194 return key.Num == v.Num195}196197func xlookup(args []Node, get lookup) Value {198 key := eval(args[0], get)199 if key.Kind == value.Error {200 return key201 }202 look := matrixArg(args[1], get)203 res := matrixArg(args[2], get)204 mode, err := intArg(args, 4, 0, get)205 if err != nil {206 return *err207 }208 search, err := intArg(args, 5, 1, get)209 if err != nil {210 return *err211 }212 switch {213 case !look.vector():214 return value.ErrValue215 case mode < -1 || mode > 2 || search == 0 || search < -2 || search > 2:216 return value.ErrValue217 }218 // The result range lines up with the lookup range along its length.219 n := look.size()220 var pick func(i int) Value221 switch {222 case look.cols == 1 && res.rows == n:223 pick = func(i int) Value { return res.cell(i, 0) }224 case look.rows == 1 && res.cols == n:225 pick = func(i int) Value { return res.cell(0, i) }226 default:227 return value.ErrValue228 }229 q := lineSeq(look, get, look.cols == 1)230 best, streamed := -1, false231 if get.stream && q.sparse {232 best, streamed = q.seekStream(key, mode, search < 0)233 }234 var order []int235 if !streamed {236 best, order = -1, q.order(search < 0)237 }238 for _, i := range order {239 v := look.at(i)240 if lookupEqual(key, v, mode == 2) {241 best = i242 break243 }244 if mode == 0 || mode == 2 || v.Kind != key.Kind {245 continue246 }247 // Next smaller (-1) or next larger (1): keep the closest.248 c := compare(v, key)249 if c*mode > 0 && (best < 0 || compare(v, look.at(best))*mode < 0) {250 best = i251 }252 }253 if best < 0 {254 if given(args, 3) {255 return eval(args[3], get)256 }257 return value.ErrNA258 }259 return pick(best)260}