Sobre las claves 1, 3, 5, 5, 5, 7, 9 en A1:A7, la fórmula XMATCH(5, A1:A7, 0, 2) devuelve 3. Cambie solo el último argumento a -1 y la misma fórmula devuelve 5. Apunte la primera versión a una columna que alguien re-ordenó por otro campo, y un 5 perfectamente 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 reporte que lleva hasta aquí rara vez menciona modos de búsqueda. 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 que la fórmula se escribió. Las secciones de abajo recorren las reglas de a una, con el resultado exacto que cada fórmula produce
¿Qué valores de match_mode y search_mode acepta XLOOKUP?
HotXLS, el componente de hojas de cálculo nativo de Delphi y C++Builder, evalúa XLOOKUP y XMATCH a través de una rutina de búsqueda compartida que acepta exactamente cuatro modos de coincidencia y cuatro modos de búsqueda. match_mode puede ser 0 (exacto), -1 (exacto o el siguiente menor), 1 (exacto o el siguiente mayor) o 2 (comodín). search_mode puede ser 1 (escaneo de primero a último), -1 (escaneo 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 ajusta 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 las fórmulas escritas a mano
Los conteos de argumentos se chequean antes de todo eso. 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 que la evaluación arranque. Los argumentos de modo en sí son expresiones de hoja ordinarias, 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 vuelve search mode 1, -1.5 se vuelve -1, y 2.9 se vuelve 2. El truncamiento 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. Escriba el modo como literal, o envuelva uno calculado en ROUND
El programa siguiente escribe una tabla de tasas pequeña y cinco búsquedas que ejercitan esas reglas. Los comentarios dan el valor que cada celda de fórmula carga después de 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;
// Valores default: match_mode 0, search_mode 1
Sheet.Cells[1, 4].Formula := 'XLOOKUP(30,A1:A4,B1:B4)'; // 0.15
// Exacto o el 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 modo de búsqueda
Sheet.Cells[4, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,3)'; // #VALUE!
// La coincidencia de comodines no puede correr en 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 le cree. Chequear la afirmación tomaría una pasada completa por la columna, que es exactamente el costo que una búsqueda binaria existe para evitar, así que el descenso reduce el intervalo a la mitad en cada sondeo y devuelve donde termine. Microsoft documenta el mismo contrato para XLOOKUP y XMATCH: los modos binarios exigen datos ordenados y dan resultados inválidos de lo contrario. ISO/IEC 29500-1 §18.17, la cláusula de fórmulas de SpreadsheetML, describe a los más viejos LOOKUP y VLOOKUP con su propio requisito de orden ascendente; XLOOKUP y XMATCH vinieron después y se guardan en el archivo como _xlfn.XLOOKUP y _xlfn.XMATCH bajo el prefijo de función futura
La figura traza XLOOKUP(40, A1:A3, B1:B3, "missing", 0, 2) sobre las claves 40, 10, 30. El primer sondeo 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 carga 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 rankea los valores por tipo 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 cargan los códigos como texto no es ascendente bajo esa regla, por más ordenada que se vea. El texto se compara sin distinguir mayúsculas, y en Windows el orden viene del locale del usuario a través de CompareStringW, que maneja guiones y letras acentuadas distinto que un ordenamiento por bytes. Una columna de claves ordenada por una base de datos con collation binaria puede entonces fallar la promesa de orden exactamente en los códigos que llevan puntuación
Que los blancos rankeen al final tiene una consecuencia ladeada. Una columna de claves ascendente con filas vacías abajo sigue siendo ascendente, así que apuntar 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 directo hacia los blancos
var
R: TXLSFormulaEvaluationResult;
begin
// B1:B3 carga 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.
// El primer sondeo lee la B5 vacía, los blancos rankean sobre todo número,
// así que el descenso sigue moviéndose a la derecha y nunca vuelve
// Recorte el rango a las filas llenas 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 modo de búsqueda elige el extremo. Bajo search mode 2 el descenso binario registra cada clave igual que 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 acierto en dirección de escaneo: el índice más bajo para search mode 1, el más alto para search mode -1
Por eso los reportes de discrepancia se agrupan en filas particulares. Un archivo de muestra con claves únicas da la misma respuesta bajo los cuatro modos de búsqueda, así que cada 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 precisamente en las filas repetidas, y el código que escribió las fórmulas no cambió para nada
// A1:A7 carga 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 acierto hacia adelante
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)'; // 5, primer acierto hacia atrás
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)'; // 3, índice más bajo de la racha
// B1:B7 carga 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 finalista
Cuando ninguna celda es igual al valor de búsqueda, el match mode -1 devuelve el valor más grande que no es mayor que el objetivo y el match mode 1 devuelve el más pequeño que no es menor. En los modos lineales HotXLS escanea el vector completo y mantiene un mejor candidato, así que la coincidencia aproximada funciona sobre datos sin ordenar. Una celda reemplaza al candidato vigente solo cuando es estrictamente más cercana, nunca cuando apenas lo empata. Entre varias celdas que cargan el mismo valor finalista, la que se conserva es la primera encontrada en orden de escaneo
Tome las claves 10, 30, 20, 30, 50, 40 en A1:A6. XMATCH(35, A1:A6, -1, 1) devuelve 2: el primer 30 se vuelve el candidato, y el segundo 30 solo lo empata. Invierta el escaneo con XMATCH(35, A1:A6, -1, -1) y el resultado es 4, porque escaneando hacia atrás encuentra al otro 30 primero. XMATCH(35, A1:A6, 1, 1) devuelve 6, ya que 40 le gana a 50 como el valor más pequeño por encima de 35. Sobre datos ordenados los modos binarios llegan al mismo vecino en un puñado de sondeos: sobre 10, 20, 30, 40, 50, XMATCH(35, A1:A5, -1, 2) devuelve 3 y XMATCH(35, A1:A5, 1, 2) devuelve 4
Si ni un acierto exacto ni un vecino aceptable aparecen, XLOOKUP devuelve su argumento if_not_found cuando se suministró uno y #N/A cuando no. XMATCH no tiene argumento de respaldo y siempre devuelve #N/A
Por qué los comodines y la búsqueda binaria no pueden coexistir
Un patrón comodín no tiene posición en un orden de ordenamiento. El match mode 2 pregunta si una celda calza 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 manera defendible de decir si ACME-* queda a la izquierda o a la derecha de una celda dada, así que HotXLS devuelve #VALUE! para match mode 2 combinado con search mode 2 o -2 y no adivina. Use search mode 1 o -1 con comodines y acepte el costo lineal
El match mode 2 siempre aplica reglas de comodín: * representa cualquier racha de caracteres, ? un carácter, y ~ hace literal al siguiente carácter. XLOOKUP("PN~*", ...) por eso encuentra el texto PN* mismo, lo que importa cuando los códigos de producto realmente contienen 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 ordenamiento reporta empate. Esa comparación también es insensible a mayúsculas, pero es el mismo ordenamiento consciente del locale descrito arriba, que es la única relación que una búsqueda binaria puede legítimamente usar. Las hojas con muchas búsquedas también moldean lo que el tracking de dependencias detrás del recálculo incremental tiene que re-evaluar después de una edición
Errores de forma y porciones 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 al mismo tiempo devuelve #VALUE!; HotXLS no elige un eje por usted. XLOOKUP agrega una segunda regla: el rango de retorno debe tener el mismo largo 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 nunca se descarta 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 completa emparejada como un arreglo. Se derrama en las celdas vecinas bajo las mismas reglas que las otras funciones de arreglos dinámicos, cubiertas en el artículo sobre rangos de derrame y arreglos dinámicos. Las celdas vacías dentro de una porción derramada vuelven como 0. Sacar un registro completo de una tabla con una fórmula es conveniente, y también es la manera más rápida de sobrescribir una columna que casualmente se sentaba a la derecha de la fórmula
Elegir un modo para workbooks que nadie vigila
La generación del lado del servidor merece una política más estricta que el uso interactivo, porque ninguna persona mira el resultado antes de que salga. Deje match mode 0 con search mode 1 en ese lugar: la coincidencia es exacta, el escaneo ignora el orden de filas, y re-ordenar la hoja no puede cambiar la respuesta. El search mode 2 pertenece solo donde el mismo código produjo el ordenamiento, en la misma corrida, sobre la misma columna. Una búsqueda binaria sobre una columna ordenada por una clave distinta es la manera más barata de calcular con confianza un número equivocado
Cuando sí cambie una búsqueda a modo binario, chequee el ordenamiento usted mismo en el punto donde emite la fórmula. La función de abajo no es parte de HotXLS; es un guardia para agregar a su propio código. Para muchas búsquedas exactas contra una tabla, la propiedad RecalcLookupCache del workbook deja que Recalculate reuse el primer escaneo de un rango para búsquedas posteriores contra él. La caché se apaga sola cuando la pasada contiene OFFSET, INDIRECT, referencias circulares o tablas de datos What-If, y los resultados son iguales de cualquier manera
// Un guardia suyo, no una API de HotXLS: confirme que una columna de claves
// numérica está ascendente antes de emitir una fórmula search_mode 2 que dependa de ella
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, vacío 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 guardia es deliberadamente conservador: una sola celda vacía o de texto manda la columna completa de vuelta a la ruta lineal. En una columna ordenada y caliente el pago 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 sondeos 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 su propio código Pascal suele ser más claro que cualquier arreglo de funciones incorporadas; el artículo sobre funciones de hoja de cálculo personalizadas muestra cómo registrar una. XLOOKUP, XMATCH y el resto del motor de fórmulas descrito aquí son parte del HotXLS Delphi spreadsheet component, y su página de producto lista las funciones de hoja soportadas para Delphi y C++Builder