Sobre las claves 1, 3, 5, 5, 5, 7, 9 en A1:A7, la fórmula XMATCH(5, A1:A7, 0, 2) devuelve 3. Cambia solo el último argumento a -1 y la misma fórmula devuelve 5. Apunta la primera versión a una columna que alguien re-ordenó por otro campo, y un 5 claramente visible en la hoja puede volver como #N/A. Las tres respuestas son correctas. El último argumento de XMATCH, como el sexto de XLOOKUP, son dos cosas a la vez: una instrucción sobre qué duplicado preferir, y una promesa sobre cómo están ordenados los datos
El informe que lleva hasta aquí rara vez menciona los search modes. Dice que un workbook generado discrepa de lo que alguien esperaba en un puñado de filas, y esas filas comparten algo: una clave repetida, una coincidencia aproximada que tuvo que elegir un vecino, o una columna de búsqueda cuyo orden cambió después de escribir la fórmula. Las secciones de abajo recorren las reglas una a una, con el resultado exacto que produce cada fórmula
Qué valores de match_mode y search_mode acepta XLOOKUP
HotXLS, el componente de hojas de cálculo nativo para Delphi y C++Builder, evalúa XLOOKUP y XMATCH a través de una única rutina de búsqueda compartida que acepta exactamente cuatro match modes y cuatro search modes. match_mode puede ser 0 (exacto), -1 (exacto o siguiente menor), 1 (exacto o siguiente mayor) o 2 (wildcard). search_mode puede ser 1 (escaneo de primero a último), -1 (de último a primero), 2 (búsqueda binaria sobre datos ascendentes) o -2 (búsqueda binaria sobre datos descendentes). Cualquier otro número devuelve #VALUE!; nada se recorta al modo legal más cercano. Omitir los argumentos selecciona match mode 0 y search mode 1, que es lo que usan la mayoría de fórmulas escritas a mano
El conteo de argumentos se comprueba antes que nada. XLOOKUP toma de tres a seis argumentos y XMATCH de dos a cuatro, y una llamada fuera de esos rangos es un #VALUE! antes de empezar la evaluación. Los argumentos de modo son a su vez expresiones de hoja corrientes, así que HotXLS convierte cada uno a número, devuelve #VALUE! para NaN, infinito o un valor fuera del rango de entero de 32 bits, y después trunca hacia cero. 1.5 se convierte en search mode 1, -1.5 en -1, y 2.9 en 2. El truncado muerde cuando el modo viene de una celda calculada por aritmética: un valor que se muestra como 2 pero está guardado como 1.9999999 selecciona un escaneo lineal. Escribe el modo como literal, o envuelve uno calculado en ROUND
El siguiente programa escribe una pequeña tabla de tasas y cinco búsquedas que ejercitan esas reglas. Los comentarios dan el valor que cada celda de fórmula guarda tras el Recalculate, derivado de las reglas de arriba
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Rates'); // Sheets[1]
Sheet.Cells[1, 1].Value := 10; Sheet.Cells[1, 2].Value := 0.25;
Sheet.Cells[2, 1].Value := 20; Sheet.Cells[2, 2].Value := 0.20;
Sheet.Cells[3, 1].Value := 30; Sheet.Cells[3, 2].Value := 0.15;
Sheet.Cells[4, 1].Value := 40; Sheet.Cells[4, 2].Value := 0.10;
// Por defecto: match_mode 0, search_mode 1
Sheet.Cells[1, 4].Formula := 'XLOOKUP(30,A1:A4,B1:B4)'; // 0.15
// Exacto o siguiente menor, búsqueda binaria sobre claves ascendentes
Sheet.Cells[2, 4].Formula := 'XLOOKUP(35,A1:A4,B1:B4,"none",-1,2)'; // 0.15
// 1.5 se trunca a search_mode 1
Sheet.Cells[3, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,1.5)'; // 0.10
// 3 no es un search mode
Sheet.Cells[4, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,3)'; // #VALUE!
// La coincidencia con wildcards no puede correr sobre una búsqueda binaria
Sheet.Cells[5, 4].Formula := 'XLOOKUP("4*",A1:A4,B1:B4,"none",2,2)'; // #VALUE!
if Book.Recalculate = lxOk then
Book.SaveAs('lookup-modes.xlsx');
finally
Book.Free;
end;
end;¿Por qué search_mode 2 no encuentra una clave que está en la hoja?
El search mode 2 le dice a HotXLS que el vector de búsqueda ya está ordenado ascendente, y el motor se lo cree. Comprobar la afirmación costaría una pasada completa por la columna, que es justo el coste que una búsqueda binaria existe para evitar, así que el descenso parte el intervalo a la mitad en cada sonda y devuelve donde acabe. Microsoft documenta el mismo contrato para XLOOKUP y XMATCH: los modos binarios exigen datos ordenados y dan resultados inválidos en caso contrario. ISO/IEC 29500-1 §18.17, la cláusula de fórmulas de SpreadsheetML, describe al viejo LOOKUP y VLOOKUP con su propio requisito de orden ascendente; XLOOKUP y XMATCH llegaron después y se guardan en el archivo como _xlfn.XLOOKUP y _xlfn.XMATCH bajo el prefijo de funciones futuras
La figura traza XLOOKUP(40, A1:A3, B1:B3, "missing", 0, 2) sobre las claves 40, 10, 30. La primera sonda aterriza en la fila 2, lee 10, y como 10 está por debajo de 40 descarta las filas 1 y 2, incluida la fila que contiene el 40. La fila 3 lee 30, también por debajo de 40, y el intervalo queda vacío. La fórmula devuelve "missing". Con search mode 1 la misma llamada encuentra el 40 en la fila 1
"Ascendente" también significa algo más específico de lo que parece en pantalla. La ruta binaria ordena los valores por clase antes de compararlos: primero números, luego texto, luego booleanos, luego valores de error, luego blancos. Una columna de códigos de parte numéricos en la que tres celdas guardan los códigos como texto no es ascendente bajo esa regla, por ordenada que parezca. El texto se compara sin distinguir mayúsculas, y en Windows el orden viene del locale del usuario a través de CompareStringW, que trata guiones y letras acentuadas de forma distinta a una ordenación por bytes. Una columna de claves ordenada por una base de datos con collation binaria puede por tanto fallar la promesa de orden justamente en los códigos que llevan puntuación
Que los blancos ordenen últimos tiene una consecuencia ladeada. Una columna de claves ascendente con filas vacías por debajo sigue siendo ascendente, así que apuntar el search mode 2 a un rango sobredimensionado como A2:A5001 es inofensivo. Una columna descendente seguida de filas vacías ya no es descendente, y el search mode -2 camina derecho hacia los blancos
var
R: TXLSFormulaEvaluationResult;
begin
// B1:B3 contiene 9, 7, 5; B4:B10 están vacías
R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-2)');
// R.Status = xlfeOk y VarIsError(R.Value): el resultado es #N/A.
// La primera sonda lee la B5 vacía, los blancos ordenan por encima de todo número,
// así que el descenso sigue hacia la derecha y nunca vuelve
// Recorta el rango a las filas con datos y la misma llamada funciona
R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B3,0,-2)'); // 3
// Un escaneo lineal se salta los blancos y encuentra la clave donde esté
R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-1)'); // 3
end;¿Dónde aterrizan las claves duplicadas?
XMATCH y XLOOKUP aterrizan en un extremo fijo de una racha de duplicados, y el search mode elige el extremo. Bajo search mode 2 el descenso binario registra cada clave igual que se encuentra y sigue estrechando hacia la izquierda, así que el resultado es el índice más bajo de la racha. Bajo search mode -2, sobre datos descendentes, registra la posición y estrecha hacia la derecha, dando el índice más alto. Los modos lineales devuelven el primer impacto en la dirección de escaneo: el índice más bajo para search mode 1, el más alto para search mode -1
Por eso los informes de discrepancia se agrupan en filas concretas. Un archivo de muestra con claves únicas da la misma respuesta bajo los cuatro search modes, así que todo test construido a partir de él pasa. En cuanto los datos de producción repiten un código de cliente, los modos empiezan a discrepar justamente en las filas repetidas, y el código que escribió las fórmulas no ha cambiado nada
// A1:A7 contiene 1, 3, 5, 5, 5, 7, 9 (ascendente, una racha de tres)
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)'; // 3, primer impacto hacia delante
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)'; // 5, primer impacto hacia atrás
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)'; // 3, índice más bajo de la racha
// B1:B7 contiene 9, 7, 5, 5, 5, 3, 1 (descendente)
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)'; // 5, índice más alto de la rachaCómo elige la coincidencia aproximada al subcampeón
Cuando ninguna celda iguala el valor buscado, el match mode -1 devuelve el mayor valor que no supera al objetivo y el match mode 1 devuelve el menor valor que no se queda por debajo. En los modos lineales HotXLS escanea el vector entero y conserva un mejor candidato, así que la coincidencia aproximada funciona sobre datos sin ordenar. Una celda sustituye al candidato en curso solo cuando es estrictamente más cercana, nunca cuando solo empata. Entre varias celdas que guarden el mismo valor subcampeón, la que se queda es la primera encontrada en el orden de escaneo
Toma las claves 10, 30, 20, 30, 50, 40 en A1:A6. XMATCH(35, A1:A6, -1, 1) devuelve 2: el primer 30 se convierte en candidato, y el segundo 30 solo lo empata. Voltea el escaneo con XMATCH(35, A1:A6, -1, -1) y el resultado es 4, porque escanear hacia atrás se encuentra antes con el otro 30. XMATCH(35, A1:A6, 1, 1) devuelve 6, ya que 40 le gana a 50 como el menor valor por encima de 35. Sobre datos ordenados los modos binarios llegan al mismo vecino en un puñado de sondas: sobre 10, 20, 30, 40, 50, XMATCH(35, A1:A5, -1, 2) devuelve 3 y XMATCH(35, A1:A5, 1, 2) devuelve 4
Si no aparece ni un impacto exacto ni un vecino aceptable, XLOOKUP devuelve su argumento if_not_found cuando se suministró y #N/A cuando no. XMATCH no tiene argumento de fallback y siempre devuelve #N/A
Por qué los wildcards y la búsqueda binaria no pueden convivir
Un patrón wildcard no tiene posición en un orden de clasificación. El match mode 2 pregunta si una celda encaja con una máscara, que es una pregunta de sí o no, mientras que un descenso binario necesita una respuesta de tres vías que diga qué mitad conservar. No hay forma defendible de decir si ACME-* queda a la izquierda o a la derecha de una celda dada, así que HotXLS devuelve #VALUE! para el match mode 2 combinado con search mode 2 o -2 y no adivina. Usa search mode 1 o -1 con wildcards y asume el coste lineal
El match mode 2 aplica siempre reglas de wildcard: * representa cualquier racha de caracteres, ? un carácter, y ~ vuelve literal al siguiente carácter. XLOOKUP("PN~*", ...) encuentra por tanto el texto PN* mismo, lo que importa cuando los códigos de producto llevan de verdad asteriscos. En match mode 0 el escaneo lineal compara texto sin distinguir mayúsculas, y la ruta binaria trata dos valores como iguales cuando su comparación de orden reporta empate. Esa comparación también ignora mayúsculas, pero es la misma ordenación consciente del locale descrita arriba, que es la única relación que una búsqueda binaria puede usar legítimamente. Las hojas cargadas de búsquedas también moldean lo que el tracking de dependencias detrás de la recalculación incremental tiene que re-evaluar tras una edición
Errores de forma y rebanadas de retorno derramadas
Ambas funciones necesitan un rango de búsqueda unidimensional. Un rango que abarca más de una fila y más de una columna a la vez devuelve #VALUE!; HotXLS no elige un eje por ti. XLOOKUP añade una segunda regla: el rango de retorno debe tener la misma longitud que el rango de búsqueda a lo largo del eje de coincidencia. Una búsqueda vertical sobre 500 filas emparejada con un rango de retorno de 499 filas es un error, y la última fila jamás se suelta en silencio. Cuando el rango de búsqueda es una sola celda, la forma del rango de retorno decide si la búsqueda cuenta como vertical u horizontal
Un rango de retorno más ancho que una columna, para una búsqueda vertical, hace que XLOOKUP devuelva la fila emparejada entera como un array. Se derrama en las celdas vecinas bajo las mismas reglas que las demás funciones de dynamic array, cubiertas en el artículo sobre spill ranges y dynamic arrays. Las celdas vacías dentro de una rebanada derramada vuelven como 0. Sacar un registro completo de una tabla con una fórmula es cómodo, y es además la manera más rápida de sobrescribir una columna que tuviera la mala suerte de estar a la derecha de la fórmula
Elegir un modo para workbooks que nadie vigila
La generación del lado servidor merece una política más estricta que el uso interactivo, porque ninguna persona mira el resultado antes de que salga. Deja allí match mode 0 con search mode 1: la coincidencia es exacta, el escaneo ignora el orden de las filas, y re-ordenar la hoja no puede cambiar la respuesta. El search mode 2 pertenece solo a donde el mismo código produjo la ordenación, en la misma ejecución, sobre la misma columna. Una búsqueda binaria sobre una columna ordenada por otra clave es la manera más barata de calcular con confianza un número equivocado
Cuando de verdad pases una búsqueda a modo binario, comprueba tú el orden en el punto donde emites la fórmula. La función de abajo no forma parte de HotXLS; es un guard para añadir a tu propio código. Para muchas búsquedas exactas contra una misma tabla, la propiedad del workbook RecalcLookupCache deja que Recalculate reutilice el primer escaneo de un rango para búsquedas posteriores contra él. La cache se auto-desactiva cuando la pasada contiene OFFSET, INDIRECT, referencias circulares o tablas de datos What-If, y los resultados son iguales de una forma u otra
// Tu propio guard, no una API de HotXLS: confirma que una columna de claves numéricas está
// ascendente antes de emitir una fórmula search_mode 2 que dependa de ello
function KeysAscending(Sheet: TXLSXWorksheet; Col, FirstRow,
LastRow: Integer): Boolean;
var
Row: Integer;
Prev, Cur: Variant;
begin
Result := False;
Prev := Sheet.Cells[FirstRow, Col].Value;
if not VarIsNumeric(Prev) then Exit;
for Row := FirstRow + 1 to LastRow do
begin
Cur := Sheet.Cells[Row, Col].Value;
if (not VarIsNumeric(Cur)) or (Cur < Prev) then Exit; // texto, blanco o fuera de orden
Prev := Cur;
end;
Result := True;
end;
// Hoja Rates: claves en A2:A5001, tasas en B2:B5001
UseBinary := KeysAscending(Rates, 1, 2, 5001);
Book.RecalcLookupCache := True;
for Row := 2 to OrderCount + 1 do
if UseBinary then
Orders.Cells[Row, 5].Formula := Format(
'XLOOKUP(B%d,Rates!$A$2:$A$5001,Rates!$B$2:$B$5001,0,-1,2)', [Row])
else
Orders.Cells[Row, 5].Formula := Format(
'XLOOKUP(B%d,Rates!$A$2:$A$5001,Rates!$B$2:$B$5001,"missing")', [Row]);El guard es deliberadamente conservador: una sola celda blanca o de texto devuelve la columna entera a la ruta lineal. En una columna ordenada y caliente el premio del modo binario es real, unos log₂ n lecturas por búsqueda en lugar de hasta n, lo que para 5.000 claves significa 13 sondas como máximo
Cuando una búsqueda es en realidad una regla de negocio disfrazada, como una tarifa por tramos con excepciones, un callback a tu propio código Pascal suele ser más claro que cualquier arreglo de funciones integradas; el artículo sobre funciones de hoja personalizadas muestra cómo registrar una. XLOOKUP, XMATCH y el resto del motor de fórmulas descrito aquí forman parte del HotXLS Delphi spreadsheet component, y su página de producto lista las funciones de hoja soportadas para Delphi y C++Builder