Fórmula genérica
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Resumen
Con grandes conjuntos de datos, la búsqueda exacta de VLOOKUP puede ser dolorosamente lenta, pero puede hacer que VLOOKUP se aclare rápidamente usando dos VLOOKUPS, como se explica a continuación.
Notas:
- 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.
- Debe ordenar los datos por valor de búsqueda para que este truco funcione.
- Este ejemplo usa rangos con nombre. Si no desea utilizar rangos con nombre, utilice referencias absolutas.
VLOOKUP de coincidencia exacta es lento
Cuando usa BUSCARV en "modo de coincidencia exacta" en un gran conjunto 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 llevar minutos.
La coincidencia exacta se establece proporcionando FALSE o cero como cuarto argumento:
=VLOOKUP(val,data,col,FALSE)
La razón por la que BUSCARV en este modo es lento es porque debe verificar cada registro en el conjunto de datos hasta que se encuentre una coincidencia. Esto a veces se denomina búsqueda lineal.
VLOOKUP de coincidencia aproximada es muy rápido
En el modo de coincidencia aproximada, BUSCARV es extremadamente rápido. Para usar BUSCARV de coincidencia aproximada, debe ordenar sus datos por la primera columna (la columna de búsqueda), luego especificar VERDADERO para el cuarto argumento:
=VLOOKUP(val,data,col,TRUE)
(BUSCARV está predeterminado en verdadero, que es un valor predeterminado aterrador, pero esa es otra historia).
Con conjuntos de datos muy grandes, cambiar a BUSCARV de coincidencia aproximada puede significar un aumento espectacular de la velocidad.
Entonces, obvio, ¿verdad? Solo ordena los datos, usa la coincidencia aproximada y listo.
No tan rápido (je).
El problema con BUSCARV en el modo de "coincidencia aproximada" es este: BUSCARV 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 VLOOKUP dos veces, ambas en modo de coincidencia aproximada:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Explicación
La primera instancia de VLOOKUP simplemente busca el valor de búsqueda (la identificación en este ejemplo):
=IF(VLOOKUP(id,data,1,TRUE)=id
y devuelve VERDADERO solo cuando se encuentra el valor de búsqueda. En ese caso,
la fórmula vuelve a ejecutar BUSCARV en modo de coincidencia aproximada para recuperar un valor de esa tabla:
VLOOKUP(id,data,col,TRUE)
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 es FALSO" de la función SI, y puede devolver el valor que desee. En este ejemplo, usamos NA () devolvemos un error # N / A, pero también podría devolver un mensaje como "Falta" o "No encontrado".
Recuerde: debe ordenar los datos por valor de búsqueda para que este truco funcione.
