面向 Delphi 和 C++Builder 的原生电子表格组件 HotXLS，用同一套查找核心来求值 XLOOKUP 和 XMATCH。这个核心接受四种匹配模式（-1、0、1、2）和四种查找模式（-2、-1、1、2），只要查找模式的绝对值是 2 就跑一趟对数级的二分下降，其余任何组合都会以公式错误拒绝
把你带到这里的那份 bug 报告从来不会说"查找模式"。它说的是：服务端生成的工作簿显示的数字，和用 Excel 打开同一份文件时不一样，大概九千行里有四行不对。这四行总有共同点：一个重复的查找键，或者一次不得不选一个邻居来凑数的近似匹配，或者一个上周被别人按另一列重新排过序的查找列。查找函数正是公式引擎不再只是算术、开始变成一份契约的地方，而这份契约里有不少条款，大多数调用方从来没读过
XLOOKUP 到底能接受哪些模式编号
各自恰好四个，没有别的。HotXLS 在碰到任何一个单元格之前，先对照 -1、0、1、2 校验 match_mode，对照 -2、-1、1、2 校验 search_mode，任何其他值都会返回 #VALUE!，而不是被钳制到最接近的合法模式上。四种匹配模式分别是：0 表示精确匹配，-1 表示精确匹配或次小值，1 表示精确匹配或次大值，2 表示通配符匹配；四种查找模式分别是：1 表示正向线性扫描，-1 表示反向线性扫描，2 表示对升序数据做二分查找，-2 表示对降序数据做二分查找。省略这两个参数时默认选中匹配模式 0 和查找模式 1，这也是几乎所有实际公式会用到的组合。参数个数的校验方式也一样严格：XLOOKUP 接受三到六个参数，XMATCH 接受两到四个，超出这个范围的一律在求值开始之前就返回 #VALUE!
// Shared by XLOOKUP and XMATCH, before any cell is read
if ((RequestedMatchMode <> -1) and (RequestedMatchMode <> 0) and
(RequestedMatchMode <> 1) and (RequestedMatchMode <> 2)) or
((RequestedSearchMode <> -2) and (RequestedSearchMode <> -1) and
(RequestedSearchMode <> 1) and (RequestedSearchMode <> 2)) then
begin
Result := lxErrorValue; // #VALUE!
Exit;
end;
if Abs(RequestedSearchMode) = 2 then
begin
if RequestedMatchMode = 2 then // wildcards cannot ride a binary descent
begin
Result := lxErrorValue;
Exit;
end;
// ... O(log n) descent over the lookup vector
end;再往前一步，还有一处不太起眼、但值得了解的检查。模式参数是作为工作表表达式传入的，所以 HotXLS 会先把它们强制转换成数字，拒绝 NaN 和无穷大，然后要求这个数字必须等于它自身四舍五入后的值。XLOOKUP(x, A:A, B:B, "none", 0, 1.5) 会返回 #VALUE!，而不会被当成伪装过的查找模式 2。当这个模式值来自一个由高频取整运算产生的单元格时，这一点就很重要，而这种情况在自动生成的工作簿里比在手写的工作簿里常见得多
为什么 search_mode 2 在未排序数据上会给出错误答案
因为它做的正是你要求它做的事。查找模式 2 告诉引擎，查找向量已经是升序排列的，而二分查找没有办法在不做一趟会抵消其存在意义的 O(n) 扫描的情况下去验证这个声明。所以 HotXLS 选择相信调用方，不断把区间对半砍，然后把下降过程最终落到的位置返回给你。在未排序输入上，得到的答案不是一个错误，而是一个悄无声息的错误结果，这属于违反契约，而不是引擎本身的缺陷
微软对 XLOOKUP 和 XMATCH 记录了同样的不对称性：二分模式要求数据已排序，否则产生无效结果。定义 SpreadsheetML 公式语法的 ISO 29500-1 第 18.17 条，里面带的是更早的 LOOKUP 和 VLOOKUP 描述，它们自己也有升序要求，而 XLOOKUP 和 XMATCH 出现得比那段文字晚得多，晚到它们在文件里是按"未来函数"约定写成 _xlfn.XLOOKUP 和 _xlfn.XMATCH 的。虽然出现的年代不同，交易条件却是一样的：调用方提供有序性这个不变量，引擎提供对数复杂度
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Rates');
Sheet.Cells[1, 1].Value := 40; Sheet.Cells[1, 2].Value := 0.10;
Sheet.Cells[2, 1].Value := 10; Sheet.Cells[2, 2].Value := 0.25;
Sheet.Cells[3, 1].Value := 30; Sheet.Cells[3, 2].Value := 0.15;
// Forward linear scan: finds key 40 wherever it sits
Sheet.Cells[5, 1].Formula := 'XLOOKUP(40,A1:A3,B1:B3,"missing",0,1)';
// Binary ascending: the promise was broken, the key is never visited
Sheet.Cells[6, 1].Formula := 'XLOOKUP(40,A1:A3,B1:B3,"missing",0,2)';
Book.SaveAs('lookup-modes.xlsx');
finally
Book.Free;
end;
end;跟踪第二条公式，失败过程完全是机械化的。下降过程探查中间那个单元格，读到 10，判断 10 比 40 小，于是丢弃左半区间——恰恰包含真正存着 40 的那一行，接着探查 30，再次丢弃，区间就用完了。Excel 的行为完全一样，而这正是关键所在：复现这个错误答案是一项兼容性要求，不是什么将就。有序性这个前提，比"数字递增"要严格得多，因为比较器会先按类型对值排序，顺序是数字，然后是文本，然后是布尔值，然后是错误值，然后是空白，只有在同一类型内部才会继续比较。一列数值型的部件编码，如果其中有三个单元格存的是文本，那么无论屏幕上看起来多么像是升序，在这个比较器眼里它就不是升序，二分模式会心安理得地读错它
重复键最终会落在哪里
落在重复项这段区间里一个确定的端点上，而具体是哪一端取决于查找模式，不是运气。当二分下降在查找模式 2 下碰到一个相等的键时，它会记录这个位置，然后继续向左收窄，所以结果是这段重复区间里最小的索引；在查找模式 -2 下，作用在降序数据上，它会记录位置并向右收窄，所以结果是最大的索引。线性模式简单得多：查找模式 1 返回正向遇到的第一个命中，查找模式 -1 返回反向遇到的第一个命中。这正是本文开头那"四行不一致"背后的细节，因为一份键都唯一的工作簿，在这四种查找模式下给出的答案完全相同，也就把这个差异藏在了你用干净样本文件写的每一个测试背后。往生产数据里加一个重复的客户编码，各个模式就会恰恰在那些重复的行上开始给出不同的答案：引擎里什么都没变，只是输入不再是一个集合，变成了一个多重集合
// A1:A7 holds 1, 3, 5, 5, 5, 7, 9 - ascending, with a run of three
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)'; // 3, first forward hit
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)'; // 5, first reverse hit
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)'; // 3, lowest index of the run
// B1:B7 holds 9, 7, 5, 5, 5, 3, 1 - descending
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)'; // 5, highest index of the run近似匹配是怎样选出候补的
做法是在做精确匹配搜索的同时维护一个当前最优候选，只有在没有精确命中时才返回它。HotXLS 把 match_mode 为 -1 理解为"不大于目标值的最大值"，把 match_mode 为 1 理解为"不小于目标值的最小值"，两者都是在整个被扫描区域内求解出来的，而不是碰到第一个可接受的邻居就停下。在二分路径里，同样的思路顺带就从下降过程里得到了：每一步只要越过或没到目标，都会更新候选，所以最终候选就是紧挨着"键本该被插入的那个位置"的边界元素
// Linear path: refine the candidate only on a strict improvement
if (RequestedMatchMode = -1) or (RequestedMatchMode = 1) then
begin
CompareResult := CompareDynamicValues(CurrentValue, RequestedValue);
if ((RequestedMatchMode = -1) and (CompareResult <= 0) and
((CandidateIndex < 0) or
(CompareDynamicValues(CurrentValue, CandidateValue) > 0))) or
((RequestedMatchMode = 1) and (CompareResult >= 0) and
((CandidateIndex < 0) or
(CompareDynamicValues(CurrentValue, CandidateValue) < 0))) then
begin
CandidateIndex := ScanIndex;
CandidateValue := CurrentValue;
end;
end;仔细看这个内层条件，因为并列时的取舍就藏在这里。一个新单元格只有在严格优于当前候选时才会取代它，仅仅相等是不够的，所以在若干个持有相同候补值的单元格中，最终保留的是扫描顺序里最先遇到的那一个：正向扫描下是最小索引，反向扫描下是最大索引。如果 XLOOKUP 和 XMATCH 既没找到精确命中，也没找到可接受的邻居，XLOOKUP 会在提供了 if_not_found 参数时退回到该参数，没提供时返回 #N/A，而 XMATCH 一律返回 #N/A
为什么通配符和二分查找无法共存
因为一个通配符模式表达的不是有序关系里的一个位置。匹配模式 2 问的是某个单元格是否匹配某个掩码，掩码匹配只能回答是或否；而二分下降需要的是一个能告诉它该保留哪一半的三态答案。没有任何站得住脚的方式能回答 ACME-* 相对某个给定单元格是偏左还是偏右，所以 HotXLS 一开始就用 #VALUE! 拒绝 match_mode 2 和 search_mode 2 或 -2 的组合，而不是去猜一种排序方式、然后生产出一个看着还算合理的胡说结果。这两条路径判断相等的方式也不一样，这进一步印证了这种拆分：线性扫描用不区分大小写的文本比较来判断相等，开启通配符时则用掩码匹配；而二分下降则是向排序比较器索要一个零值来判断相等。这是刻意的设计，不是分层实现带来的意外，因为二分路径只能使用它实际据以导航的那种关系。如果你需要通配符，就用查找模式 1 或 -1，接受线性开销，这和增量重算背后的依赖跟踪机制被设计出来、专门用来不让你的关键路径背上这份开销，是同一种取舍
形状错误：二维范围与不匹配的返回向量
两个函数都要求查找范围必须是真正一维的。如果传入的范围同时跨了不止一行、又不止一列，HotXLS 会返回 #VALUE!，而不是替你选一个轴，单行或单列的范围则会沿其长轴方向读取。XLOOKUP 还多加了一条形状规则：返回范围沿匹配轴的长度必须和查找范围完全一致，所以一次跨 500 行的纵向查找，配上一个只有 499 行的返回范围，是一个错误，而不是在最后一行悄悄被消化掉的差一错误。当纵向查找的返回范围宽度超过一列，或者横向查找的返回范围高度超过一行时，XLOOKUP 会把整个匹配到的切片作为一个数组返回，并按其他动态数组函数同样的规则溢出到相邻单元格，详情见溢出范围与动态数组一文。这个特性在用一条公式从表里取出整条记录时确实很有用，但同时也是最快能覆盖掉一列你本想保留的数据的方式
没人盯着屏幕时该怎么选模式
服务端生成场景理应采用比交互式使用更严格的策略，因为没有人会去注意到一个总计数字看起来不对。站得住脚的默认选择是查找模式 1 配匹配模式 0：线性、精确、不依赖顺序，也不可能因为重新给某张表排序而失效。只有在同一条代码路径、同一次运行、同一列数据也负责产生这份有序性时，才去用查找模式 2，并且要把这个依赖关系写在公式旁边，因为在一列按另一个键排序过的数据上做二分查找，是能够以最低成本算出一个"信心满满的错误数字"的办法。当查找确实很热、数据也确实排好序时，收益是真实的：下降过程读取的单元格数量是 log n 量级，而不是 n，而每一次读取都要经过一次完整的工作簿单元格解析，所以省下的开销比指令数量本身暗示的要大得多
如果问题的形状更接近一条业务规则，而不是一次查找，回调到你自己的 Pascal 代码——详情见自定义工作表函数一文——通常会比任何对内置函数的巧妙排列组合都更胜一筹。这里讨论的 XLOOKUP 和 XMATCH 实现，随标准版 HotXLS Delphi 电子表格组件一起提供，其产品页带有面向 Delphi 和 C++Builder 的完整函数支持参考