Repository Public
1import Foundation2import Testing3import SheetCore4@testable import FunctionsLookup56/// Excel behaviour that LibreOffice cannot arbitrate (it differs or lacks the feature), written by hand.7@Suite struct LookupEdgeCaseTests {8 /// A:number key (unsorted), B:text, C:number; E: sorted ascending; G: mixed types.9 func sample() -> Book {10 let b = Book()11 b.column("A1", [30, 10, 20, 10, 50])12 b.column("B1", ["thirty", "ten", "twenty", "TEN-bis", "fifty"])13 b.column("C1", [3, 1, 2, 1.5, 5])14 b.column("E1", [1, 2, 4, 8, 16, 32])15 b.column("G1", [5, "5", true, nil, .error(.div0), "", 0, false, "abc"])16 return b17 }1819 @Test func typeAwareMatching() {20 let b = sample()21 // text "5" never matches the number 5 and vice versa22 #expect(b.eval("=MATCH(\"5\",G1:G9,0)") == 2)23 #expect(b.eval("=MATCH(5,G1:G9,0)") == 1)24 #expect(b.eval("=VLOOKUP(\"10\",A1:C5,2,FALSE)") == NA)25 // logical values only match logical values (TRUE is not 1, FALSE is not 0)26 #expect(b.eval("=MATCH(TRUE,G1:G9,0)") == 3)27 #expect(b.eval("=MATCH(1,G1:G9,0)") == NA)28 #expect(b.eval("=MATCH(0,G1:G9,0)") == 7)29 #expect(b.eval("=MATCH(FALSE,G1:G9,0)") == 8)30 #expect(b.eval("=XMATCH(FALSE,G1:G9)") == 8)31 // case-insensitive text32 #expect(b.eval("=MATCH(\"ABC\",G1:G9,0)") == 9)33 #expect(b.eval("=XLOOKUP(\"TEN\",B1:B5,A1:A5)") == 10)34 // errors inside the lookup range are skipped, an error lookup value propagates35 #expect(b.eval("=MATCH(0,G1:G9,0)") == 7)36 #expect(b.eval("=MATCH(1/0,G1:G9,0)") == .error(.div0))37 #expect(b.eval("=VLOOKUP(NA(),A1:C5,2,FALSE)") == NA)38 #expect(b.eval("=XLOOKUP(SQRT(-1),A1:A5,B1:B5,\"x\")").isError)39 }4041 @Test func blankHandling() {42 let b = sample()43 // classic functions: an empty lookup cell counts as 0; empty cells in the range never match 044 #expect(b.eval("=MATCH(Z99,G1:G9,0)") == 7)45 #expect(b.eval("=MATCH(\"\",G1:G9,0)") == 6)46 // XMATCH/XLOOKUP: an empty lookup value matches the first empty cell or empty string47 #expect(b.eval("=XMATCH(Z99,G1:G9)") == 4)48 #expect(b.eval("=XMATCH(\"\",G1:G9)") == 4)49 // a found empty cell is returned as blank (displayed 0 in a cell)50 b.set("K1", "=VLOOKUP(20,A1:D5,4,FALSE)")51 #expect(b.get("K1") == 0)52 }5354 @Test func exactMatchReturnsFirstAndReverseReturnsLast() {55 let b = sample()56 #expect(b.eval("=VLOOKUP(10,A1:C5,2,FALSE)") == "ten")57 #expect(b.eval("=MATCH(10,A1:A5,0)") == 2)58 #expect(b.eval("=XMATCH(10,A1:A5,0,-1)") == 4)59 #expect(b.eval("=XLOOKUP(10,A1:A5,B1:B5,,0,-1)") == "TEN-bis")60 }6162 @Test func approximateOnSortedData() {63 let b = sample()64 #expect(b.eval("=MATCH(8,E1:E6)") == 4)65 #expect(b.eval("=MATCH(9,E1:E6,1)") == 4)66 #expect(b.eval("=MATCH(0.5,E1:E6,1)") == NA)67 #expect(b.eval("=MATCH(1000,E1:E6,1)") == 6)68 #expect(b.eval("=VLOOKUP(31.9,E1:E6,1)") == 16)69 #expect(b.eval("=LOOKUP(5,E1:E6)") == 4)70 // text lookup in a numeric column and the reverse find nothing71 #expect(b.eval("=MATCH(\"a\",E1:E6,1)") == NA)72 #expect(b.eval("=MATCH(5,{\"a\",\"b\",\"c\"},1)") == NA)73 // numbers sort before text: the binary search lands on the last number74 #expect(b.eval("=MATCH(99,{1,2,3,\"a\",\"b\"},1)") == 3)75 #expect(b.eval("=MATCH(\"b\",{1,2,3,\"a\",\"b\",\"c\"},1)") == 5)76 // duplicates: approximate match returns the last equal item77 #expect(b.eval("=MATCH(2,{1,2,2,2,3},1)") == 4)78 // trailing and interior empty cells do not disturb the search79 #expect(b.eval("=MATCH(8,E1:E20,1)") == 4)80 #expect(b.eval("=MATCH(100,E1:E20,1)") == 6)81 b.column("M1", [1, 2, nil, nil, 5, 6, nil, 8])82 for (k, want) in [(1.0, 1.0), (2, 2), (4.5, 2), (5, 5), (7, 6), (8, 8), (9, 8)] {83 #expect(b.eval("=MATCH(\(k),M1:M8,1)") == .number(want), "key \(k)")84 }85 // descending data, match_type -1: smallest value ≥ the key86 #expect(b.eval("=MATCH(5,{9,7,5,3,1},-1)") == 3)87 #expect(b.eval("=MATCH(6,{9,7,5,3,1},-1)") == 2)88 #expect(b.eval("=MATCH(10,{9,7,5,3,1},-1)") == NA)89 #expect(b.eval("=MATCH(0,{9,7,5,3,1},-1)") == 5)90 }9192 @Test func wildcards() {93 let b = Book()94 b.column("A1", ["apple", "apricot", "a*b", "what?", "tilde~x", "banana", ""])95 b.column("B1", [1, 2, 3, 4, 5, 6, 7])96 #expect(b.eval("=MATCH(\"ap*\",A1:A7,0)") == 1)97 #expect(b.eval("=MATCH(\"*an*\",A1:A7,0)") == 6)98 #expect(b.eval("=MATCH(\"a~*b\",A1:A7,0)") == 3)99 #expect(b.eval("=MATCH(\"what~?\",A1:A7,0)") == 4)100 #expect(b.eval("=MATCH(\"tilde~~x\",A1:A7,0)") == 5)101 #expect(b.eval("=MATCH(\"?????\",A1:A7,0)") == 1)102 #expect(b.eval("=MATCH(\"??????\",A1:A7,0)") == 6)103 #expect(b.eval("=VLOOKUP(\"B?N*\",A1:B7,2,FALSE)") == 6)104 // XLOOKUP/XMATCH treat * and ? literally unless match_mode is 2105 #expect(b.eval("=XMATCH(\"ap*\",A1:A7)") == NA)106 #expect(b.eval("=XMATCH(\"a*b\",A1:A7)") == 3)107 #expect(b.eval("=XMATCH(\"ap*\",A1:A7,2)") == 1)108 #expect(b.eval("=XLOOKUP(\"*t\",A1:A7,B1:B7,,2)") == 2)109 #expect(b.eval("=XLOOKUP(\"*t\",A1:A7,B1:B7,\"none\")") == "none")110 // wildcards never match numbers111 #expect(b.eval("=MATCH(\"*\",B1:B7,0)") == NA)112 // approximate matching ignores wildcards113 #expect(b.eval("=VLOOKUP(\"ap*\",A1:B2,2,TRUE)") == NA)114 #expect(Wildcard("a*c?e").matches("ABxxCdE"))115 #expect(!Wildcard("a*c?e").matches("abxxcde!"))116 #expect(Wildcard("**").matches(""))117 #expect(!Wildcard("?").matches(""))118 #expect(Wildcard("~").matches("~"))119 }120121 @Test func errorResults() {122 let b = sample()123 #expect(b.eval("=VLOOKUP(10,A1:C5,0,FALSE)") == .error(.value))124 #expect(b.eval("=VLOOKUP(10,A1:C5,4,FALSE)") == .error(.ref))125 #expect(b.eval("=HLOOKUP(30,A1:C5,6,FALSE)") == .error(.ref))126 #expect(b.eval("=VLOOKUP(99,A1:C5,2,FALSE)") == NA)127 #expect(b.eval("=MATCH(10,A1:B5,0)") == NA) // two-dimensional lookup array128 #expect(b.eval("=XMATCH(10,A1:B5)") == .error(.value))129 #expect(b.eval("=XMATCH(10,A1:A5,3)") == .error(.value))130 #expect(b.eval("=XMATCH(10,A1:A5,0,0)") == .error(.value))131 #expect(b.eval("=XMATCH(\"a*\",B1:B5,2,2)") == .error(.value)) // wildcard with binary search132 #expect(b.eval("=XLOOKUP(10,A1:A5,B1:B4)") == .error(.value)) // size mismatch133 #expect(b.eval("=XLOOKUP(10,A1:B5,C1:C5)") == .error(.value))134 #expect(b.eval("=LOOKUP(0,E1:E6)") == NA)135 #expect(b.eval("=ADDRESS(0,1)") == .error(.value))136 #expect(b.eval("=ADDRESS(1,16385)") == .error(.value))137 #expect(b.eval("=ADDRESS(1,1,9)") == .error(.value))138 #expect(b.eval("=AREAS(5)") == .error(.value))139 #expect(b.eval("=VLOOKUP(10,A1:C5,2,\"x\")") == .error(.value))140 }141142 @Test func omittedAndEmptyRangeLookupArguments() {143 let b = sample()144 #expect(b.eval("=VLOOKUP(9,E1:E6,1)") == 8) // omitted → approximate145 #expect(b.eval("=VLOOKUP(9,E1:E6,1,)") == NA) // empty → FALSE (exact)146 #expect(b.eval("=MATCH(9,E1:E6,)") == NA) // empty match_type → 0147 #expect(b.eval("=VLOOKUP(9,E1:E6,1,1)") == 8)148 #expect(b.eval("=VLOOKUP(9,E1:E6,1,0)") == NA)149 }150151 @Test func xlookupReturnsReferences() {152 let b = sample()153 // the result is a reference, so it can be a range endpoint or feed reference functions154 #expect(b.eval("=SUM(XLOOKUP(10,A1:A5,C1:C5):XLOOKUP(50,A1:A5,C1:C5))") == 9.5)155 #expect(b.eval("=ROW(XLOOKUP(20,A1:A5,C1:C5))") == 3)156 #expect(b.eval("=COLUMN(XLOOKUP(20,A1:A5,C1:C5))") == 3)157 #expect(b.eval("=COLUMNS(XLOOKUP(20,A1:A5,B1:C5))") == 2)158 #expect(b.eval("=SUM(OFFSET(XLOOKUP(30,A1:A5,C1:C5),0,0,3,1))") == 6)159 // with array constants a value comes back160 #expect(b.eval("=XLOOKUP(2,{1,2,3},{\"a\",\"b\",\"c\"})") == "b")161 #expect(b.eval("=XLOOKUP(2,{1;2;3},{\"a\",\"x\";\"b\",\"y\";\"c\",\"z\"})") == grid([["b", "y"]]))162 // if_not_found is evaluated and returned as is163 #expect(b.eval("=XLOOKUP(99,A1:A5,C1:C5,\"n/a\")") == "n/a")164 #expect(b.eval("=SUM(XLOOKUP(99,A1:A5,C1:C5,E1:E3))") == 7)165 #expect(b.eval("=XLOOKUP(99,A1:A5,C1:C5)") == NA)166 }167168 @Test func xlookupSpillsIntoCells() {169 let b = sample()170 b.set("J1", "=XLOOKUP(20,A1:A5,B1:C5)")171 #expect(b.get("J1") == "twenty")172 #expect(b.get("K1") == 2)173 b.set("J3", "=XLOOKUP(E1:E3,A1:A5,B1:B5,\"-\")") // one result per lookup value174 #expect(b.get("J3") == "-")175 #expect(b.get("J5") == "-")176 b.set("J8", "=XLOOKUP({10;50;7},A1:A5,C1:C5,0)")177 #expect([b.get("J8"), b.get("J9"), b.get("J10")] == [1, 5, 0])178 // dependents recalculate when the looked-up data changes179 b.set("C3", 22)180 #expect(b.get("K1") == 22)181 }182183 @Test func approximateModesOnUnsortedData() {184 let b = sample()185 // XLOOKUP match modes -1 / 1 scan the whole range (no sorting required)186 #expect(b.eval("=XLOOKUP(25,A1:A5,B1:B5,,-1)") == "twenty")187 #expect(b.eval("=XLOOKUP(25,A1:A5,B1:B5,,1)") == "thirty")188 #expect(b.eval("=XLOOKUP(5,A1:A5,B1:B5,\"below\",-1)") == "below")189 #expect(b.eval("=XLOOKUP(60,A1:A5,B1:B5,\"above\",1)") == "above")190 #expect(b.eval("=XMATCH(15,A1:A5,-1)") == 2) // first of the two 10s191 #expect(b.eval("=XMATCH(15,A1:A5,-1,-1)") == 4) // last of the two 10s192 #expect(b.eval("=XMATCH(\"m\",{\"z\",\"a\",\"k\",\"p\"},-1)") == 3)193 #expect(b.eval("=XMATCH(\"m\",{\"z\",\"a\",\"k\",\"p\"},1)") == 4)194 }195196 @Test func binarySearchModes() {197 let b = sample()198 #expect(b.eval("=XMATCH(8,E1:E6,0,2)") == 4)199 #expect(b.eval("=XMATCH(9,E1:E6,0,2)") == NA)200 #expect(b.eval("=XMATCH(9,E1:E6,-1,2)") == 4)201 #expect(b.eval("=XMATCH(9,E1:E6,1,2)") == 5)202 #expect(b.eval("=XMATCH(64,E1:E6,1,2)") == NA)203 #expect(b.eval("=XMATCH(0,E1:E6,-1,2)") == NA)204 #expect(b.eval("=XMATCH(9,{32,16,8,4,2,1},-1,-2)") == 3)205 #expect(b.eval("=XMATCH(9,{32,16,8,4,2,1},1,-2)") == 2)206 #expect(b.eval("=XMATCH(4,{32,16,8,4,2,1},0,-2)") == 4)207 #expect(b.eval("=XLOOKUP(\"kilo\",{\"alpha\",\"golf\",\"kilo\",\"zulu\"},{1,2,3,4},,0,2)") == 3)208 }209210 @Test func lookupVectorAndArrayForms() {211 let b = sample()212 #expect(b.eval("=LOOKUP(4,E1:E6,{\"a\";\"b\";\"c\";\"d\";\"e\";\"f\"})") == "c")213 #expect(b.eval("=LOOKUP(5,E1:E6,{\"a\",\"b\",\"c\",\"d\",\"e\",\"f\"})") == "c") // result vector may be a row214 #expect(b.eval("=LOOKUP(2,{1,2,3;\"x\",\"y\",\"z\"})") == "y") // wider than tall: first row → last row215 #expect(b.eval("=LOOKUP(2,{1,\"x\";2,\"y\";3,\"z\"})") == "y") // otherwise first column → last column216 #expect(b.eval("=LOOKUP(2,{1,2;3,4})") == 2) // square: first column {1;3} → row 1 → last column217 #expect(b.eval("=LOOKUP(4,E1:E6,C1:C2)") == NA) // result vector too short218 #expect(b.eval("=LOOKUP({1;4;9},E1:E6)") == grid([[1], [4], [8]]))219 }220221 @Test func referenceHelpers() {222 let b = sample()223 #expect(b.eval("=ADDRESS(2,3)") == "$C$2")224 #expect(b.eval("=ADDRESS(2,3,2)") == "C$2")225 #expect(b.eval("=ADDRESS(2,3,3)") == "$C2")226 #expect(b.eval("=ADDRESS(2,3,4)") == "C2")227 #expect(b.eval("=ADDRESS(2,3,2,FALSE)") == "R2C[3]")228 #expect(b.eval("=ADDRESS(2,3,1,FALSE,\"[Book1]Sheet1\")") == "'[Book1]Sheet1'!R2C3")229 #expect(b.eval("=ADDRESS(2,3,1,TRUE,\"EXCEL SHEET\")") == "'EXCEL SHEET'!$C$2")230 #expect(b.eval("=ADDRESS(1,1,4,TRUE,\"Data\")") == "Data!A1")231 #expect(b.eval("=ADDRESS(1048576,16384,4)") == "XFD1048576")232 #expect(b.eval("=SUM(INDIRECT(ADDRESS(1,5)&\":\"&ADDRESS(3,5)))") == 7)233 #expect(b.eval("=AREAS(A1:C5)") == 1)234 #expect(b.eval("=HYPERLINK(\"https://example.com\",\"Example\")") == "Example")235 #expect(b.eval("=HYPERLINK(\"https://example.com\")") == "https://example.com")236 #expect(b.eval("=HYPERLINK(\"https://example.com\",C5)") == 5)237 #expect(b.eval("=TRANSPOSE({1,2;3,4;5,6})") == grid([[1, 3, 5], [2, 4, 6]]))238 #expect(b.eval("=TRANSPOSE(A1:B2)") == grid([[30, 10], ["thirty", "ten"]]))239 #expect(b.eval("=SUM(TRANSPOSE(E1:E6)*{1,1,1,1,1,1})") == 63)240 b.set("J1", "=TRANSPOSE(E1:E3)")241 #expect([b.get("J1"), b.get("K1"), b.get("L1")] == [1, 2, 4])242 }243244 @Test func liftedKeysUseTheHashIndexConsistently() {245 // more than 4 keys against more than 16 items takes the hash-index path; it must agree with the linear scan246 let b = Book()247 var values = [Value?]()248 for i in 0..<400 { values.append(i % 7 == 3 ? .text("Key\(i % 50)") : .number(Double((i * 37) % 101))) }249 values[13] = .bool(true); values[200] = .bool(false); values[77] = nil; values[90] = .text("")250 b.column("A1", values)251 b.column("B1", (0..<400).map { .number(Double($0)) })252 let keys: [String] = ["5", "100", "\"key3\"", "\"KEY45\"", "TRUE", "FALSE", "\"missing\"", "101", "\"ke*\"", "0", "\"\""]253 let list = "{" + keys.joined(separator: ";") + "}"254 for fn in ["MATCH(%@,A1:A400,0)", "XMATCH(%@,A1:A400)", "XMATCH(%@,A1:A400,0,-1)", "XMATCH(%@,A1:A400,2)",255 "VLOOKUP(%@,A1:B400,2,FALSE)", "XLOOKUP(%@,A1:A400,B1:B400,-1)", "XLOOKUP(%@,A1:A400,B1:B400,-1,0,-1)"] {256 guard case .array(let lifted) = b.eval("=" + fn.replacingOccurrences(of: "%@", with: list)) else { Issue.record("no array for \(fn)"); continue }257 #expect(lifted.rows == keys.count && lifted.cols == 1)258 for (i, k) in keys.enumerated() {259 #expect(lifted.items[i] == b.eval("=" + fn.replacingOccurrences(of: "%@", with: k)), "\(fn) key \(k)")260 }261 }262 }263264 @Test func wholeColumnExactMatchIsFast() {265 let b = Book()266 let n = 200_000267 b.engine.transaction("fill") {268 for i in 0..<n {269 b.engine.set(sheet: 0, row: i, col: 0, .value(.number(Double(i * 2))))270 b.engine.set(sheet: 0, row: i, col: 1, .value(.number(Double(i))))271 }272 }273 b.engine.recalculate()274 let clock = ContinuousClock()275 var result = Value.blank276 let single = clock.measure { result = b.eval("=VLOOKUP(399998,A:B,2,FALSE)") }277 #expect(result == .number(199_999))278 #expect(single < .milliseconds(400), "single whole-column lookup took \(single)")279 // 2 000 lookup values against the same columns: one hash index instead of 2 000 scans280 let many = clock.measure { result = b.eval("=SUM(XLOOKUP(SEQUENCE(2000,1,0,200),A:A,B:B,0))") }281 #expect(result == .number((0..<2000).reduce(0.0) { $0 + Double($1 * 100) }))282 #expect(many < .milliseconds(1500), "2 000 lifted lookups took \(many)")283 #expect(b.eval("=MATCH(123456,A:A,0)") == 61729)284 #expect(b.eval("=XMATCH(123457,A:A)") == NA)285 #expect(b.eval("=MATCH(123457,A:A,1)") == 61729)286 }287}288