BUSCARV: enumera todos los elementos coincidentes
En la publicación anterior de esta serie, Dominar la BUSCARV , aprendimos cómo buscar el segundo, tercer, cuarto o enésimo elemento de un conjunto de datos. Hoy, ampliaremos eso viendo cómo devolver todos los valores coincidentes en el conjunto de datos. Además, combinaremos esto con coincidencias parciales, para que podamos crear una función de búsqueda completa y potente.
Enumere todos los elementos coincidentes
BUSCARV es excelente cuando tienes datos únicos, pero eso no siempre sucede. Volvamos a un ejemplo utilizado en una publicación anterior , mire la captura de pantalla a continuación.
Hay 4 personas diferentes llamadas Paul que trabajan para su empresa. La última vez que usamos este ejemplo sabíamos el apellido de Paul. Imagínate que esta vez alguien ha pedido el número de teléfono de Paul, pero no recuerda su apellido. ¿Cuál Pablo? Una BUSCARV básica por sí sola sería inútil ya que solo se devolvería el primer número de teléfono de Paul, Paul Daniels. Necesitamos poder enumerar todos los elementos coincidentes.
Podemos usar la fórmula introducida en la publicación anterior para devolver una lista completa de todos los Paul.
La celda G7 incluye la siguiente fórmula:
{=SIERROR(BUSCARV(PEQUEÑO(SI(SI(ESERROR(BUSCAR($F$3,$A$2:$A$8" "$B$2:$B$8)),FALSO,VERDADERO),FILA($A$2: $A$8)),$E7),ELEGIR({1,2},FILA($A$2:$A$8),$C$2:$C$8),2,0),"")}Esta fórmula se ha copiado a G8-G11.
¿Cómo funciona esta fórmula?
Desglosemos esta fórmula poco a poco.
{=SIERROR(BUSCARV(PEQUEÑO(SI(SI(ESERROR(BUSCAR($F$3,$A$2:$A$8" "$B$2:$B$8)),FALSO,VERDADERO),FILA($A$2: $A$8)),$E7),ELEGIR({1,2},FILA($A$2:$A$8),$C$2:$C$8),2,0),"")}Recuerde, esta es una fórmula matricial, por lo que debe usar Ctrl+Shift+Enter para usar la fórmula.
Buscando función
{=SIERROR(BUSCARV(PEQUEÑO(SI(SI(ESERROR(BUSCAR($F$3,$A$2:$A$8" "$B$2:$B$8)),FALSO,VERDADERO),FILA($A$2: $A$8)),$E7),ELEGIR({1,2},FILA($A$2:$A$8),$C$2:$C$8),2,0),"")}La función BÚSQUEDA se utiliza para encontrar un fragmento de texto dentro de otro fragmento de texto. La diferencia clave entre BUSCAR y ENCONTRAR es que BUSCAR no distingue entre mayúsculas y minúsculas. La celda F3 contiene el valor de búsqueda; "Pablo".
Las celdas A2-A8 se combinan con las celdas B2-B8 para crear un nombre completo. La variedad de nombres {Paul Daniels, Jack Daniels, Paul Newman, Paul McCartney, Jeff Daniels, Paul Simon, Anthony Daniels}. El resultado de la función BÚSQUEDA es la posición de la cadena coincidente. Si no se encuentra ninguna coincidencia, ¡el #VALOR! se devuelve el error. Al usar “Paul” como ejemplo, el resultado que devolvió es {1, #¡VALOR!, 1, 1, #¡VALOR!, 1, #¡VALOR!}
Función ESERROR
{=SIERROR(BUSCARV(PEQUEÑO(SI(SI(ESERROR(BUSCAR($F$3,$A$2:$A$8" "$B$2:$B$8)),FALSO,VERDADERO),FILA($A$2: $A$8)),$E7),ELEGIR({1,2},FILA($A$2:$A$8),$C$2:$C$8),2,0),"")}La función ESERROR rodea el resultado devuelto por la función BÚSQUEDA. Esta función convertirá cualquier error en VERDADERO y cualquier no ERROR en FALSO. El resultado devuelto ahora se convierte a {FALSO, VERDADERO, FALSO, FALSO, VERDADERO, FALSO, VERDADERO}
Función SI
{=SIERROR(BUSCARV(PEQUEÑO(SI(SI(ESERROR(BUSCAR($F$3,$A$2:$A$8" "$B$2:$B$8)),FALSO,VERDADERO),FILA($A$2: $A$8)),$E7),ELEGIR({1,2},FILA($A$2:$A$8),$C$2:$C$8),2,0),"")}La función doble IF se utiliza para invertir los valores VERDADERO/FALSO, luego, si es VERDADERO, recupera el número de FILA.
La función SI que rodea ISERROR devolverá {VERDADERO, FALSO, VERDADERO, VERDADERO, FALSO, VERDADERO, FALSO}. Entonces SI rodea ese SI devolverá {2, FALSO, 4, 5, FALSO, 7, FALSO}
Función PEQUEÑA
{=SIERROR(BUSCARV(PEQUEÑO(SI(SI(ESERROR(BUSCAR($F$3,$A$2:$A$8" "$B$2:$B$8)),FALSO,VERDADERO),FILA($A$2: $A$8)),$E7),ELEGIR({1,2},FILA($A$2:$A$8),$C$2:$C$8),2,0),"")}La función PEQUEÑA se utiliza para devolver la enésima búsqueda, según el valor de las celdas E7-E11.
El resto de la fórmula.
{=SIERROR(BUSCARV(PEQUEÑO(SI(SI(ESERROR(BUSCAR($F$3,$A$2:$A$8" "$B$2:$B$8)),FALSO,VERDADERO),FILA($A$2: $A$8)),$E7),ELEGIR({1,2},FILA($A$2:$A$8),$C$2:$C$8),2,0),"")}La sección roja anterior se explicó en la publicación anterior , así que consulte allí para obtener una descripción más completa.
Función SI ERROR
La función IFERROR se utiliza para devolver una celda en blanco para cualquier valor que no coincida.
{=SIERROR(BUSCARV(PEQUEÑO(SI(SI(ESERROR(BUSCAR($F$3,$A$2:$A$8" "$B$2:$B$8)),FALSO,VERDADERO),FILA($A$2: $A$8)),$E7),ELEGIR({1,2},FILA($A$2:$A$8),$C$2:$C$8),2,0),"")}El resultado
Cuando se copia desde la celda G7-G11, BUSCARV devolverá una lista de todos los elementos coincidentes.
Devolviendo el nombre completo
Es posible que hayamos encontrado los números de teléfono de cada Paul, pero también necesitamos recuperar el nombre completo. La fórmula en F7 es similar a la de G7 (con las diferencias resaltadas a continuación). Nuestra fórmula devolverá el nombre y apellido separados por un espacio.
=SIERROR(BUSCARV(PEQUEÑO(SI(SI(ESERROR(BUSCAR($F$3,$A$2:$A$8" "$B$2:$B$8)),FALSO,VERDADERO),FILA($A$2:$ A$8)),$E7),ELEGIR({1,2},FILA($A$2:$A$8),$A$2:$A$8" "$B$2:$B$8),2,0) ,"")Devolver coincidencias parciales
Como hemos utilizado la función BÚSQUEDA, es capaz de devolver coincidencias parciales. Si buscamos "Dan", nos proporcionará una lista de todos los resultados coincidentes.
Esta fórmula también puede funcionar con comodines. Si buscamos “paul*m” hay 3 resultados coincidentes
Descargue la hoja de referencia de BUSCARV avanzada
Descargue la hoja de referencia de BUSCARV avanzada. Incluye la mayoría de los consejos y trucos que hemos cubierto en esta serie, incluidos cálculos más rápidos, criterios múltiples, búsqueda por la izquierda y mucho más.
Descárgalo y fíjalo en el trabajo; incluso puedes reenviarlo a tus amigos y compañeros de trabajo.
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre del archivo: 0166 BUSCARV Avanzada.pdf
Tener acceso
Otras publicaciones en la serie Mastering VLOOKUP
- Cómo utilizar BUSCARV
- BUSCARV: ¿Qué hace la declaración Verdadero/Falso?
- BUSCARV: Cómo calcular más rápido
- Cómo BUSCAR V a la izquierda
- BUSCARV: cambia el número de columna automáticamente
- BUSCARV con múltiples criterios
- Usando comodines con BUSCARV
- Cómo usar BUSCARV con columnas y filas
- Expandir automáticamente el rango de datos BUSCARV
- BUSCARV: busque el enésimo elemento (sin columnas auxiliares)
- BUSCARV: enumera todos los elementos coincidentes
- Hoja de referencia avanzada de BUSCARV