01 / 10BUSCARV Ignora las Mayúsculas y Dos ID Fueron Uno

BúsquedasBUSCARXBUSCARV

Búsqueda que distingue mayúsculas en Excel con IGUAL

Tamsin Kitchens Renovó por 22.500 £ en Vez de 64.800 £, Porque BUSCARV Leyó la Cuenta Lm2XP Como Lm2Xp

17 nov 20268 min de lectura

UsaBUSCARXINDICE COINCIDIRBUSCARVSUMAPRODUCTO

Las cartas de renovación de Fernside Software toman cada valor de contrato del CRM por el ID de cuenta. A Tamsin Kitchens se le ofrecieron 22.500 £ en vez de 64.800 £, firmó, y se llevó 42.300 £ al año de los ingresos.

ID de cuentaCuentaCartaValor en el CRM
0015g00000Lm2XpCorran Foods22.500 £22.500 £
0015g00000Lm2XPTamsin Kitchens22.500 £64.800 £

Alcance. IGUAL, INDICE, COINCIDIR, SUMAPRODUCTO y CONTAR.SI funcionan en todas las versiones de Excel. BUSCARX necesita Excel 2021 o Microsoft 365.

Antes de Excel 365, INDICE con COINCIDIR(VERDADERO;IGUAL(...);0) hay que confirmarla con Ctrl+Mayús+Intro. La versión con SUMAPRODUCTO de la sección 5 nunca lo necesita.

01BUSCARV Ignora las Mayúsculas y Dos ID Fueron Uno

El CRM asigna ID de cuenta de 15 caracteres, y en ellos mayúsculas y minúsculas son caracteres distintos. Lm2Xp y Lm2XP son dos clientes. Las funciones de búsqueda de Excel no opinan lo mismo.

La exportación del CRM está en una hoja llamada CRM, con los ID en A2:A9 y los valores en C2:C9. La hoja de renovaciones buscaba cada ID con esta fórmula en C2:

=BUSCARV(A2;CRM!$A$2:$C$9;3;FALSO)

Renovaciones de Fernside Software, valor del contrato por ID de cuenta

Ocho cuentas en A2:F9 de la hoja Renovaciones. A es el ID de cuenta del CRM, de 15 caracteres y sensible a mayúsculas, y B el nombre de la cuenta. La exportación del CRM está en una hoja llamada CRM, con los mismos ID en A2:A9 y el valor del contrato en C2:C9, 234.000 £ en total. C es lo que devolvió BUSCARV, D la búsqueda con IGUAL, E el CONTAR.SI de cada ID en la hoja CRM y F el recuento con IGUAL.

ABCDEF
1
Account ID
Account
VLOOKUP
EXACT lookup
COUNTIF
EXACT count
2
0015g00000KqRtA
Brightline Studios
48000
48000
2
1
3
0015g00000Lm2Xp
Corran Foods
22500
22500
2
1
4
0015g00000KqRta
Pellow & Daughters
48000
9600
2
1
5
0015g00000Nw7Hc
Ostrander Legal
31200
31200
1
1
6
0015g00000Lm2XP
Tamsin Kitchens
22500
64800
2
1
7
0015g00000Rb9Ke
Quayside Dental
18000
18000
1
1
8
0015g00000Tz3Mf
Halden Freight
27300
27300
1
1
9
0015g00000Vy6Ng
Merrow Print
12600
12600
1
1

fxLas celdas con fórmulas están resaltadas en verde

Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas

FALSO pide una coincidencia exacta, y la obtuvo, en el sentido de Excel. Coincidencia exacta en BUSCARV significa las mismas letras, en mayúsculas o minúsculas.

Para Tamsin Kitchens, en la fila 6, encontró antes Lm2Xp en la fila 3 y se detuvo ahí.

Escenario: Pon los ocho ID y valores en una hoja llamada CRM, con los mismos ID en A2:A9 de otra hoja. Escribe =BUSCARV(A6;CRM!$A$2:$C$9;3;FALSO) y comprueba que devuelve 22.500 para Lm2XP.

Pruébalo en la cuadrícula

02Gana la Primera Coincidencia, en Ambos Sentidos

Una búsqueda que ignora mayúsculas devuelve la que aparezca primero de cada pareja. La segunda cuenta de cada pareja recibe el valor de la primera, sea mayor o menor.

CuentaBUSCARVValor en el CRMError
Pellow & Daughters48.000 £9.600 £+38.400 £
Tamsin Kitchens22.500 £64.800 £−42.300 £
Las ocho cuentas230.100 £234.000 £−3.900 £

Los dos errores casi se anulan. Un total de renovaciones 3.900 £ por debajo del CRM parece un redondeo o un cambio tardío, así que nadie lo investigó.

El daño estaba en las cartas de cada cuenta. Pellow & Daughters recibió una carta por 48.000 £, cinco veces su contrato, y llamó. Esa llamada fue la primera señal de que algo fallaba.

Escenario: Añade =SUMA(C2:C9) bajo la columna de BUSCARV y =SUMA(CRM!C2:C9) al lado. Comprueba que la diferencia es solo 3.900 aunque dos cuentas fallen en más de 38.000 cada una.

Pruébalo en la cuadrícula

03IGUAL Es la Única Comparación Que Ve Mayúsculas

Todas las comparaciones de texto normales de Excel ignoran las mayúsculas. =A2=A4 compara KqRtA con KqRta y devuelve VERDADERO.

Lo mismo hacen COINCIDIR, BUSCARX, CONTAR.SI, SUMAR.SI.CONJUNTO y el = dentro de SI. IGUAL compara dos textos carácter a carácter, mayúsculas incluidas:

=A2=A4          → VERDADERO
=IGUAL(A2;A4)   → FALSO
=IGUAL(A2;A2)   → VERDADERO

Con un rango en vez de una celda, IGUAL(CRM!$A$2:$A$9;A2) devuelve ocho valores VERDADERO/FALSO, uno por fila del CRM. En un CRM limpio, exactamente uno es VERDADERO si el ID existe. Todas las soluciones que siguen buscan ese VERDADERO.

Escenario: En una celda vacía, escribe =A2=A4 y después =IGUAL(A2;A4). Comprueba que la primera devuelve VERDADERO y la segunda FALSO para KqRtA y KqRta.

Pruébalo en la cuadrícula

04Busca VERDADERO en la Matriz de IGUAL

En vez de buscar el ID, busca VERDADERO en la lista de resultados de IGUAL. La posición del único VERDADERO es la fila con las mayúsculas correctas.

Con BUSCARX, en D2:

=BUSCARX(VERDADERO;IGUAL(CRM!$A$2:$A$9;A2);CRM!$C$2:$C$9;"No encontrado")

La forma corta, =BUSCARX(VERDADERO;IGUAL(CRM!$A$2:$A$9;A2);CRM!$C$2:$C$9), devuelve #N/D si falta el ID en vez del texto. Las dos devuelven 64.800 para Tamsin Kitchens.

En versiones anteriores, INDICE y COINCIDIR hacen el mismo trabajo:

=INDICE(CRM!$C$2:$C$9;COINCIDIR(VERDADERO;IGUAL(CRM!$A$2:$A$9;A2);0))

El 0 de COINCIDIR no es opcional: sin él, COINCIDIR hace una búsqueda binaria en una lista de VERDADERO y FALSO y cae en cualquier sitio.

CuentaBUSCARVBúsqueda con IGUAL
Brightline Studios48.000 £48.000 £
Pellow & Daughters48.000 £9.600 £
Tamsin Kitchens22.500 £64.800 £

Escenario: Escribe =BUSCARX(VERDADERO;IGUAL(CRM!$A$2:$A$9;A2);CRM!$C$2:$C$9) en D2 y cópiala hasta D9. Comprueba que D4 devuelve 9.600 y D6 devuelve 64.800.

Pruébalo en la cuadrícula

05SUMAPRODUCTO Funciona en Todas las Versiones

SUMAPRODUCTO trabaja con matrices de forma nativa, así que no necesita Ctrl+Mayús+Intro en ningún Excel. Multiplica la matriz de IGUAL por los valores y suma:

=SUMAPRODUCTO(--IGUAL(CRM!$A$2:$A$9;A2);CRM!$C$2:$C$9)

El -- convierte VERDADERO y FALSO en 1 y 0. Solo la fila con las mayúsculas correctas conserva su valor; las demás quedan en cero.

Tiene dos costumbres que una búsqueda no tiene. Un ID que falta devuelve 0, no un error, y un ID que aparece dos veces devuelve la suma de los dos valores.

Por eso conviene acompañarla de un recuento, =SUMAPRODUCTO(--IGUAL(CRM!$A$2:$A$9;A2)), y fiarse del valor solo donde ese recuento sea 1.

Solo sirve para números; para un nombre o una fecha usa INDICE y COINCIDIR.

Escenario: Escribe =SUMAPRODUCTO(--IGUAL(CRM!$A$2:$A$9;A2);CRM!$C$2:$C$9) junto a la columna de BUSCARX. Cambia A6 por un ID que no esté en el CRM y comprueba que devuelve 0.

Pruébalo en la cuadrícula

06Encuentra Cada ID Que Choca

Las renovaciones necesitaban un aviso antes de enviar las cartas. CONTAR.SI ignora las mayúsculas e IGUAL no, así que comparar los dos recuentos encuentra cada ID que tiene un gemelo.

En E2 y F2, copiadas hasta la fila 9:

E2: =CONTAR.SI(CRM!$A$2:$A$9;A2)
F2: =SUMAPRODUCTO(--IGUAL(CRM!$A$2:$A$9;A2))
ID de cuentaCONTAR.SIRecuento IGUALChoque
0015g00000KqRtA21Sí
0015g00000Lm2Xp21Sí
0015g00000KqRta21Sí
0015g00000Nw7Hc11No

Cuatro de los ocho ID chocan. Brightline Studios es uno de ellos, aunque BUSCARV devolvió su propio valor: acertó solo porque iba primero.

Para una sola celda que responda por toda la hoja CRM, =SUMAPRODUCTO(--(CONTAR.SI(CRM!$A$2:$A$9;CRM!$A$2:$A$9)>1)) devuelve 4. Cualquier valor mayor que 0 significa que una búsqueda sin mayúsculas no es segura en esta tabla.

Escenario: Copia =CONTAR.SI(CRM!$A$2:$A$9;A2) en E2:E9 y =SUMAPRODUCTO(--IGUAL(CRM!$A$2:$A$9;A2)) en F2:F9. Comprueba que cuatro filas muestran 2 en E y 1 en F.

Pruébalo en la cuadrícula

07El Resto de Excel También Ignora Mayúsculas

La búsqueda no es el único sitio donde se funden las dos cuentas. Casi todo Excel trata Lm2Xp y Lm2XP como un mismo valor:

HerramientaQué hace con la pareja
Quitar duplicadosBorra la segunda fila
Tabla dinámicaUna fila, valores sumados
UNICOSDevuelve solo una
SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTOSuma las dos en un total
Combinar en Power QueryLas mantiene separadas

Power Query es la excepción: al combinar compara el texto distinguiendo mayúsculas por defecto. Una tabla dinámica hecha con la exportación del CRM habría mostrado una sola cuenta de 87.300 £.

Si el sistema de origen puede exportar una clave que no dependa de las mayúsculas, pídela. Si no, deja IGUAL en cada fórmula que toque el ID.

Nota de versión. Ninguna versión de Excel tiene un UNICOS que distinga mayúsculas.

Para listar los ID distintos respetando mayúsculas, quédate con las filas donde =SUMAPRODUCTO(--IGUAL($A$2:A2;A2)) devuelve 1, o hazlo en Power Query.

Escenario: Copia la hoja CRM, selecciona A1:C9 y ejecuta Datos > Quitar duplicados sobre la columna A. Comprueba que Excel informa de dos duplicados eliminados y que Tamsin Kitchens ha desaparecido.

Pruébalo en la cuadrícula

08Cinco Trampas

  1. Fiarse de FALSO en BUSCARV. Significa mismas letras, no mismas mayúsculas. Lm2Xp y Lm2XP coinciden.
  2. Fiarse del total. Los errores opuestos se anulan: 80.700 £ de fallos movieron el total 3.900 £.
  3. COINCIDIR sin el 0. COINCIDIR(VERDADERO;IGUAL(...)) sin tipo hace una búsqueda binaria y devuelve una fila equivocada.
  4. SUMAPRODUCTO con un ID que falta. Devuelve 0, que parece un valor de contrato real. Comprueba el recuento con IGUAL.
  5. Limpiar los ID con MAYUSC. Hace que todas las búsquedas coincidan convirtiendo dos cuentas en una para siempre.

09Cuatro Ejercicios

  1. Reprodúcelo. Monta la hoja CRM y la columna con BUSCARV, y confirma que Pellow & Daughters devuelve 48.000.
  2. Compara. Pon =A2=A4 e =IGUAL(A2;A4) una junto a otra, y confirma VERDADERO y FALSO.
  3. Arréglalo. Sustituye BUSCARV por la fórmula con BUSCARX e IGUAL, y confirma que la columna suma 234.000.
  4. Protégelo. Añade los recuentos con CONTAR.SI e IGUAL, y confirma cuatro choques con la comprobación de una celda con SUMAPRODUCTO.

Qué Llevarse

El CRM estaba bien y la fórmula no tenía erratas. Las búsquedas de Excel comparan el texto sin mayúsculas, así que dos cuentas que se distinguen por una sola letra compartieron un valor de contrato, y una carta salió a un tercio del precio.

Donde los ID distinguen mayúsculas, busca VERDADERO en una matriz de IGUAL, y cuenta cada ID con CONTAR.SI y con IGUAL antes de que nada salga de la empresa.

Comparte este artículo:
Volver al Blog
Reto diario · Día 107

Suma los kilómetros de un periodo de liquidación con SUMAR.SI.CONJUNTO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy