1package sheet23import "testing"45func TestLookupFunctions(t *testing.T) {6 checkFormulas(t, fixture(t), []fnCase{7 {`=VLOOKUP("banana", D1:E3, 2, FALSE)`, num(2.5)},8 {`=VLOOKUP("B*", D1:E3, 2, FALSE)`, num(2.5)},9 {`=VLOOKUP("kiwi", D1:E3, 2, FALSE)`, ErrNA},10 {`=VLOOKUP("Apple", D1:E5, 2, FALSE)`, num(1.5)}, // first match11 {`=VLOOKUP("Blueberry", D1:E3, 2)`, num(2.5)}, // sorted: largest <= key12 {`=VLOOKUP("Aardvark", D1:E3, 2, TRUE)`, ErrNA},13 {`=VLOOKUP(2.5, C1:D5, 2)`, txt("Banana")},14 {`=VLOOKUP("Apple", D1:E3, 3, FALSE)`, ErrRef},15 {`=VLOOKUP("Apple", D1:E3, 0, FALSE)`, ErrValue},16 {`=HLOOKUP(1.5, C1:E2, 2, FALSE)`, num(2.5)},17 {`=HLOOKUP("Apple", D1:E2, 2, FALSE)`, txt("Banana")},18 {`=MATCH("Cherry", D1:D3, 0)`, num(3)},19 {`=MATCH(3.2, C1:C7)`, num(3)},20 {`=MATCH(3.2, C1:C7, 1)`, num(3)},21 {`=MATCH(0, C1:C7)`, ErrNA},22 {`=MATCH(25, I1:I3, -1)`, num(1)}, // descending: smallest >= key23 {`=MATCH(10, I1:I3, -1)`, num(3)},24 {`=MATCH(35, I1:I3, -1)`, ErrNA},25 {`=MATCH(2, C1:D2, 0)`, ErrNA}, // not a single row or column26 {"=INDEX(D1:E3, 2, 2)", num(2.5)},27 {"=INDEX(D1:D3, 3)", txt("Cherry")},28 {"=INDEX(C1:E1, 2)", txt("Apple")}, // a single row counts across29 {"=INDEX(D1:E3, 4, 1)", ErrNum}, // as Sheets; Excel says #REF!30 {"=INDEX(D1:E3, 2)", txt("Banana")}, // a whole row, spilled31 {`=XLOOKUP("Cherry", D1:D3, E1:E3)`, num(3.5)},32 {`=XLOOKUP("kiwi", D1:D3, E1:E3)`, ErrNA},33 {`=XLOOKUP("kiwi", D1:D3, E1:E3, "none")`, txt("none")},34 {`=XLOOKUP("Apple", D1:D5, E1:E5, , 0, -1)`, num(9)}, // last to first35 {`=XLOOKUP(3.2, C1:C7, D1:D7, , -1)`, txt("Cherry")}, // next smaller36 {`=XLOOKUP(3.2, C1:C7, D1:D7, , 1)`, txt("date")}, // next larger37 {`=XLOOKUP("Ch*", D1:D3, E1:E3, , 2)`, num(3.5)},38 {`=XLOOKUP("Apple", C1:E1, C2:E2)`, txt("Banana")}, // across a row39 {`=XLOOKUP(1, C1:C3, E1:E2)`, ErrValue},40 {`=CHOOSE(2, "a", "b", "c")`, txt("b")},41 {`=CHOOSE(2.9, "a", "b", "c")`, txt("b")},42 {`=CHOOSE(4, "a", "b", "c")`, ErrNum},43 {`=CHOOSE(0, "a")`, ErrNum},44 {`=CHOOSE(1, "a", 1/0)`, txt("a")}, // only the chosen value is evaluated45 {"=ROWS(A1:C7)", num(7)},46 {"=COLUMNS(A1:C7)", num(3)},47 })48}