EXCEL: CONSULTAV más rápido con 2 CONSULTAVS

Explicación

Con grandes conjuntos de datos, la concordancia exacta de CONSULTAV puede ser extremadamente lenta, pero puede hacer que CONSULTAV se alivie rápidamente utilizando dos CONSULTAVS, como se explica a continuación.
Notas:

1. Si tiene un conjunto de datos más pequeño, este enfoque es excesivo. Úselo solo con grandes conjuntos de datos cuando la velocidad realmente cuenta.
2. Debes ordenar los datos por valor de búsqueda para que este truco funcione.
3. Este ejemplo usa rangos con nombre. Si no desea utilizar rangos con nombre, utilice referencias absolutas en su lugar.

CONSULTAV de coincidencia exacta es lento

Cuando utiliza CONSULTAV en "modo de coincidencia exacta" en un conjunto grande de datos, realmente puede ralentizar el tiempo de cálculo en una hoja de trabajo. Con, digamos, 50,000 registros, o 100,000 registros, el cálculo puede tomar minutos.
La coincidencia exacta se establece al proporcionar FALSO o cero como el cuarto argumento:

=CONSULTAV(val,data,col,FALSO)

La razón por la cual CONSULTAV en este modo es lento es porque debe verificar cada registro individual en el conjunto de datos hasta que se encuentre una coincidencia. Esto a veces se denomina búsqueda lineal.

CONSULTAV de partido aproximado es muy rápido

En el modo de coincidencia aproximada, CONSULTAV es extremadamente rápido. Para usar CONSULTAV de coincidencia aproximada, debe ordenar sus datos por la primera columna (la columna de búsqueda), luego especifique VERDADERO para el cuarto argumento:

=CONSULTAV(val,data,col,VERDADERO)

(CONSULTAV por defecto es verdadero, lo cual es un miedo por defecto, pero esa es otra historia).
Con conjuntos de datos muy grandes, cambiar a CONSULTAV de coincidencia aproximada puede significar un aumento dramático de la velocidad.
Entonces, no es obvio, ¿verdad? Simplemente ordene los datos, use una coincidencia aproximada y listo.
No tan rápido (je).
El problema con CONSULTAV en modo "coincidencia aproximada" es el siguiente: CONSULTAV no mostrará un error si el valor de búsqueda no existe. Peor aún, el resultado puede parecer completamente normal, aunque sea totalmente incorrecto (ver ejemplos). No es algo que quieras explicarle a tu jefe.
La solución es usar CONSULTAV dos veces, ambas veces en modo de coincidencia aproximada:

=SI(CONSULTAV(id,data,1,VERDADERO)=id, CONSULTAV(id,data,col,VERDADERO), NOD())

Cómo funciona esta fórmula

La primera instancia de CONSULTAV simplemente busca el valor de búsqueda (la identificación en este ejemplo):

=SI(CONSULTAV(id,data,1,VERDADERO)=id

y devuelve VERDADERO solo cuando se encuentra el valor de búsqueda. En ese caso, la fórmula ejecuta CONSULTAV nuevamente en modo de coincidencia aproximada para recuperar un valor de esa tabla:

CONSULTAV(id,data,col,VERDADERO)

No hay peligro de que falte un valor de búsqueda, ya que la primera parte de la fórmula ya se verificó para asegurarse de que esté allí.
Si no se encuentra el valor de búsqueda, se ejecuta la parte "valor si FALSO" de la función SI, y puede devolver cualquier valor que desee. En este ejemplo, usamos NOD (), devolvemos un error # N / A, pero también puede devolver un mensaje como "Falta" o "No encontrado".
Recuerde: debe ordenar los datos por valor de búsqueda para que este truco funcione.