BUSCARX con varios criterios en Excel: dos columnas

Calderbank Logistics facturó una semana de paletería por 5.590,15 £. La tabla de tarifas de la propia empresa dice que esos diez envíos valen 7.377,65 £, y cada tarifa de la factura salió de esa tabla.

La tabla tiene cada ruta dos veces: una a tarifa estándar y otra a tarifa urgente. La hoja de reservas buscaba cada envío solo por el código de ruta.

=BUSCARX(A2;$J$2:$J$11;$L$2:$L$11) coge la primera fila cuya ruta coincide y para. La fila estándar estaba escrita encima de la urgente en todos los pares, así que cinco envíos urgentes se tarifaron como trabajo estándar.

Facturado según la hoja       5.590,15
Debido según la tabla         7.377,65
Cinco envíos urgentes         1.787,50

Nada dio error. Cada celda tenía un número, cada número salió de la tabla de tarifas, y lo único que estaba mal era en cuál de las dos filas había caído la búsqueda.

Una búsqueda sobre una clave que se repite no es una fórmula que haya salido mal. Es una fórmula haciendo exactamente lo que se le pidió, sobre una pregunta que tiene dos respuestas y ninguna forma de decirlo.

01Diez Envíos, Una Tabla, Dos Filas por Ruta

Las columnas C y D son la misma tabla de tarifas leída de dos maneras. C es lo que la hoja buscó con el código de ruta; D es la tarifa que esa ruta tiene para el servicio con el que se reservó el envío.

Las dos coinciden en todos los envíos estándar y difieren en todos los urgentes. Cinco filas de diez, y las cinco que difieren son las cinco que más valen por palé.

La tabla está en J2:L11 — diez filas, cinco rutas, cada ruta una vez como Estándar y otra como Urgente:

Puesta en forma de cuadro se ve enseguida que una ruta por sí sola no puede tarifar un envío. Puesta como diez filas en una columna, que es como la guarda la hoja, parece una tabla de búsqueda corriente.

02Una Búsqueda Para en la Primera Coincidencia

BUSCARX y BUSCARV devuelven un valor, y los dos cogen la primera fila que cumple la coincidencia. Ninguno tiene forma de mencionar que una segunda fila también habría coincidido.

Sobre una clave única eso es justo lo que quieres. Sobre una clave que se repite elige en silencio, y aquello por lo que elige es la posición en la tabla.

En esta tabla la fila estándar está encima de la urgente en todas las rutas, porque ese es el orden en que alguien las escribió. Cámbialas de sitio y la misma hoja pasa a cobrar de más todos los envíos estándar.

BUSCARX puede buscar de abajo arriba, con -1 como sexto argumento. BUSCARV no puede buscar hacia atrás de ninguna manera.

Buscar desde abajo no es una solución. Cambia cuál de las dos filas te sale mal, y se rompe el día que alguien añada una ruta al final de la tabla.

03Averiguar si la Clave se Repite

Una sola fórmula lo responde para toda la columna. =SUMAPRODUCTO(--(CONTAR.SI($J$2:$J$11;$J$2:$J$11)>1)) cuenta las filas cuya ruta aparece más de una vez, y en esta tabla devuelve 10.

Diez de diez, porque todas las rutas están dos veces. Una tabla en la que solo se repite una ruta devuelve 2, y esa es la peligrosa: nueve rutas se portan bien y una no.

El mismo recuento sobre las dos columnas dice si el par es único: =SUMAPRODUCTO(--(CONTAR.SI.CONJUNTO($J$2:$J$11;$J$2:$J$11;$K$2:$K$11;$K$2:$K$11)>1)) devuelve 0 aquí.

Una clave de búsqueda solo es una clave cuando ese segundo número es 0. El primero te dice cuánta falta te hace un segundo criterio; el segundo te dice si ya lo has encontrado.

04Solución Uno: Unir las Dos Columnas en una Clave

La solución más antigua sigue funcionando en todas partes. Monta una columna auxiliar en la tabla de tarifas que pegue los dos criterios, monta la misma cadena en la hoja de reservas y busca eso:

Tabla de tarifas, M2:  =J2&"|"&K2
Hoja de reservas:      =BUSCARX(A2&"|"&B2;$M$2:$M$11;$L$2:$L$11;;0)

El separador es todo el truco. Un carácter que pueda aparecer dentro de cualquiera de los dos valores une dos pares distintos en la misma clave, que es como un J2&K2 a pelo convierte BRS + LDS01 y BRSLDS + 01 en una sola fila.

Elige algo que los datos no puedan contener — una barra vertical o una tilde — y usa el mismo a los dos lados. El precio de esta solución es una columna que mantener en cada tabla que toca.

05Solución Dos: Multiplicar las Dos Condiciones

BUSCARX admite una matriz donde va su matriz de búsqueda, así que las dos pruebas pueden vivir dentro de la fórmula y la columna auxiliar desaparece:

=BUSCARX(1;($J$2:$J$11=A2)*($K$2:$K$11=B2);$L$2:$L$11)

Cada comparación devuelve diez VERDADERO y FALSO. Multiplicarlas convierte el par en diez unos y ceros, y exactamente uno de ellos es 1: la fila donde pasaron las dos pruebas.

Ese 1 del principio es lo que la fórmula busca. No hay nada que mantener sincronizado cuando se añade una ruta, y se lee como la frase que habrías dicho en voz alta.

BUSCARX llegó con Microsoft 365 y Excel 2021. En Excel 2019 y anteriores no está, y la misma idea se escribe con INDICE y COINCIDIR — la sección siguiente.

06Solución Tres: INDICE y COINCIDIR Sobre la Misma Prueba

INDICE y COINCIDIR toman esa misma matriz de unos y ceros, y funcionan en todas las versiones de Excel que se han publicado:

=INDICE($L$2:$L$11;
   COINCIDIR(1;($J$2:$J$11=A2)*($K$2:$K$11=B2);0))

COINCIDIR devuelve la posición del primer 1 e INDICE lee esa posición en la columna de tarifas. El 0 es el argumento de coincidencia exacta, y aquí no es opcional: los unos y ceros no van en ningún orden útil.

En Microsoft 365 no hace falta nada más. En Excel 2019 y anteriores es una fórmula matricial, que se introduce con Ctrl+Mayús+Entrar, y las llaves que Excel le pone alrededor son suyas: escribirlas a mano no hace nada.

07Cuando la Respuesta es un Total, Usa SUMAR.SI.CONJUNTO

La mitad de las búsquedas escritas sobre dos criterios no son búsquedas. Cuando lo que quieres es un número que sumar, SUMAR.SI.CONJUNTO toma los criterios por pares y nunca le importa el orden de las filas:

=SUMAR.SI.CONJUNTO($L$2:$L$11;$J$2:$J$11;A2;$K$2:$K$11;B2)

En una tabla con una fila por par esto devuelve la misma tarifa que debería haber devuelto la búsqueda. En una tabla donde dos filas coinciden de verdad devuelve su suma, que está bien para líneas de factura y mal para una tarifa.

CONTAR.SI.CONJUNTO es su compañero, y es la comprobación que la búsqueda no puede hacer sola. =CONTAR.SI.CONJUNTO($J$2:$J$11;A2;$K$2:$K$11;B2) debería responder 1 al lado de cada tarifa de la hoja.

Cualquier cosa distinta de 1 es un fallo de la tabla, no de la fórmula. Dos es un duplicado que nadie ha visto; cero es una ruta que la tabla no conoce, disfrazada de #N/D o de lo que le hayas dicho a la búsqueda que ponga en su lugar.

08Cuando Dos Criterios Siguen Casando con Varias Filas

A veces el par de verdad no es único: una ruta, un servicio y tres tramos de precio por peso. Una búsqueda no puede responder a eso, porque la pregunta tiene más de una respuesta y una búsqueda devuelve una.

FILTRAR las devuelve todas: =FILTRAR($L$2:$L$11;($J$2:$J$11=A2)*($K$2:$K$11=B2)). Derramada en un hueco libre, tres filas son tres coincidencias, y la búsqueda que estabas a punto de escribir te habría enseñado una de las tres.

Cuando la regla es "la más barata de todas", dilo en la fórmula: =MIN(FILTRAR($L$2:$L$11;($J$2:$J$11=A2)*($K$2:$K$11=B2))) es una decisión puesta por escrito. Que una búsqueda caiga en la fila más barata es una casualidad, y dura hasta que alguien ordene la tabla.

09La Coincidencia Exacta No es el Valor por Defecto en Todas Partes

El cuarto argumento de BUSCARV decide si la coincidencia es exacta, y dejarlo fuera significa VERDADERO. La coincidencia aproximada sobre una tabla sin ordenar devuelve una fila equivocada con mucho aplomo en vez de #N/D.

BUSCARX va al revés: su modo de coincidencia es exacto por defecto. Ese es el valor correcto, y por sí solo ya es motivo para hacer el cambio.

Una coincidencia aproximada sobre una clave que se repite son dos fallos en una celda, y el segundo tapa al primero: la fila que te sale ni siquiera es la primera coincidencia, es donde la búsqueda binaria se haya parado.

10Qué Rompe una Clave Unida

Una clave unida es texto, y el texto es quisquilloso. La rompen tres cosas, y ninguna se ve en pantalla:

1. Un espacio al final de uno de los lados. La tabla tiene "BRS-LDS " y la hoja de reservas tiene "BRS-LDS", así que las claves se diferencian en un carácter. =ESPACIOS(J2)&"|"&ESPACIOS(K2) a los dos lados lo zanja.
2. Un número en un lado y texto en el otro. Un código de ruta 01182 tecleado en una hoja e importado en la otra llega como 1182 en una de las dos. =TEXTO(J2;"00000") a los dos lados los deja en la misma cadena.
3. Las mayúsculas. BUSCARX las ignora, así que Urgente y URGENTE casan tan contentos. Si tus datos los distinguen, una búsqueda no lo hará.

El modo de fallar es #N/D en unas filas y silencio en las demás, que es la mejor clase de fallo, porque el #N/D se ve y una tarifa equivocada no.

11Qué Debe Decir una Búsqueda que No Encuentra Nada

Todas las soluciones de arriba devuelven #N/D cuando no hay coincidencia, y esa es la respuesta correcta. Una tarifa que no se encuentra no es cero y no está en blanco.

El cuarto argumento de BUSCARX es donde se dice qué debe salir en su lugar. =BUSCARX(1;($J$2:$J$11=A2)*($K$2:$K$11=B2);$L$2:$L$11;"SIN TARIFA") pone una palabra en la línea de factura en vez de un número que alguien pueda sumar.

Envolverlo todo en SI.ERROR es el error. Caza el #N/D, y también caza el #¡REF! de una columna borrada y el #¡VALOR! de una tarifa guardada como texto, y convierte los tres en el mismo blanco pulcro.

SI.ND caza solo el fallo de coincidencia y deja pasar todo lo demás, que es la razón entera de que exista.

12Siete Cosas Que Muerden

1. Buscar por la columna que nombra la cosa. Un código de producto, una ruta, el nombre de un cliente: todos se repiten en cuanto la tabla gana una segunda dimensión.
2. Fiarse de una búsqueda porque ha devuelto un número. El fallo aquí es una tarifa verosímil de la fila equivocada, no un error que alguien pueda ver.
3. Ordenar la tabla de búsqueda. Con una clave que se repite la respuesta depende del orden de las filas, así que una ordenación cambia resultados sin cambiar ninguna fórmula.
4. Invertir la búsqueda en lugar de arreglarla. -1 elige la otra fila. No elige la buena.
5. Unir claves con un carácter que los datos contienen. Un guion dentro de un código de ruta y un guion como separador chocan, y nada en pantalla lo enseña.
6. Dejarse fuera el cuarto argumento de BUSCARV. La coincidencia aproximada sobre una tabla sin ordenar es una respuesta equivocada disfrazada de buena.
7. Envolver el resultado en SI.ERROR. Tapa el #N/D que era la única celda honesta de la fila.

13Mini Ejercicios

1. En la hoja de ejemplo, escribe =CONTAR.SI($J$2:$J$11;A2) al lado del primer envío. Espera 2, y espera 2 en las diez filas.
2. Pon la búsqueda de clave única =BUSCARX(A2;$J$2:$J$11;$L$2:$L$11) en una columna libre y la forma multiplicada al lado. Espera 38,50 y 61,00 en la primera fila.
3. Totaliza las dos columnas de tarifa contra los palés: =SUMAPRODUCTO(C2:C11;E2:E11) y =SUMAPRODUCTO(D2:D11;E2:E11). Espera 5.590,15 y 7.377,65.
4. Monta la clave unida en M2:M11 y comprueba que =BUSCARX(A2&"|"&B2;$M$2:$M$11;$L$2:$L$11;;0) devuelve 61,00 en el primer envío.
5. Borra la fila urgente de GLW-ABD de una copia de la tabla y recalcula. Comprueba que la comprobación con CONTAR.SI.CONJUNTO cae a 0 en ese envío mientras la búsqueda de clave única sigue respondiendo 36,90.

Para Quedarte Con Esto

Una búsqueda responde a la pregunta que le has hecho, y "cuál es la tarifa de esta ruta" no es la pregunta que nadie quería hacer. Siempre fue "cuál es la tarifa de esta ruta con este servicio", y la segunda mitad iba en una columna que la fórmula nunca leyó.

Por eso el fallo nunca está en la búsqueda. Está en una clave que dejó de ser única el día que la tabla ganó un segundo servicio, y en que nada en Excel marca el momento en que eso pasa.

Un CONTAR.SI.CONJUNTO al lado de la tarifa, respondiendo 1 en cada fila, habría cazado esto en la misma semana en que empezó. Cinco filas, 1.787,50 £, en una factura donde todos los números eran reales y todas las fórmulas eran correctas.