¿cómo invertir buscarv en excel con index-match?

Para comprender cómo funcionan INDEX y MATCH como fórmula de búsqueda inversa, debe elegir una cosa simple: Match le dice al índice la posición (número de celda) de un valor en una columna o fila, luego el índice devuelve este valor. valor usando esta posición (número de celda) .

Piénselo así, la función COINCIDIR es un agente encubierto que encuentra al criminal y la función ÍNDICE es un policía que posteriormente arresta a ese criminal.

Pero aprendamos en detalle cómo podemos combinar estas dos funciones. A continuación se muestra la sintaxis de INDEX como usted sabe.

ÍNDICE (matriz, núm_fila, [núm_columna])

En la función ÍNDICE, el argumento núm_fila le indica de qué fila debe devolver el valor. Digamos que si ingresa 4, devolverá el valor de la cuarta fila.

Para crear la fórmula de búsqueda inversa, debemos reemplazar COINCIDIR con argumento_fila de ÍNDICE.

Cuando usamos MATCH, busca el valor en la columna de búsqueda y devuelve el número de celda de ese valor. Y luego, INDEX usa este número para determinar la posición de la celda a partir de la columna de valor.

Al final te devuelve el valor de esa celda y obtienes el valor que buscas. Pero ahora trabajemos con un ejemplo real. A continuación tenemos una lista de ciudades y los nombres de los empleados que trabajan allí.

Aquí debemos buscar el nombre del empleado que trabaja en Mumbai. Ahora, si observa los datos, en la columna donde tiene ciudades, esa es nuestra columna de búsqueda, y en la columna donde tiene nombres de empleados, esa es su columna de valor. Y la fórmula será:

 =INDEX(B2:B11,MATCH(E3,A2:A11,0),0)

Dividamos esta fórmula en dos partes para entenderla.

Parte 1 : En la primera parte, utilizamos la función de coincidencia para buscar el valor «Mumbai» y devolvió el «5», que es la posición de la celda en la que tiene el valor «Mumbai» en la columna de ciudades.

Parte 2 : en la segunda parte, utilizamos ÍNDICE y nos referimos a la columna del nombre del empleado para encontrar el valor. Aquí la función de índice sabe que desea el valor de la quinta celda de la columna. Entonces, devolvió «Siya» en el resultado.

Más ejemplos de ÍNDICE y coincidencia

Tenemos ante nosotros algunos de los problemas comunes que hemos resuelto utilizando la fórmula INDEX MATCH. Acceda a los archivos de muestra: asegúrese de descargar estos archivos de muestra desde aquí para seguir cada ejemplo.

1. Búsqueda básica con ÍNDICE – COINCIDIR

Una búsqueda normal es una de las tareas más importantes que debe realizar con las fórmulas de búsqueda e INDEX MATCH es perfecto para eso. Aquí tenemos una tabla de datos con el ID y el nombre del empleado. Cada ID es única y debes buscar el nombre del empleado con su ID.

Digamos que desea buscar el nombre EMP-132. Para ello la fórmula será:

 =INDEX(name_column,MATCH(emp-id,emp-id_column,0))

así es como funciona esta fórmula

Primero , MATCH coincide con la identificación emp en la columna de identificación emp y devuelve el número de celda de la identificación que está buscando. Aquí el número de línea es 6.

Después de eso , ÍNDICE devuelve el nombre del empleado de la columna de nombre usando el mismo número de celda.

2. Mira a la izquierda

BUSCARV no puede ir a la izquierda cuando se busca un valor. Como mencioné, en INDEX y MATCH puedes buscar en cualquier dirección. En la tabla de datos a continuación tiene la columna del número de factura después de la columna del monto.

Entonces, si desea buscar el monto de una factura en particular, esto no es posible con BUSCARV. En BUSCARV, cuando selecciona una tabla, la primera columna de esa tabla será la columna de búsqueda.

Pero aquí en esta tabla, necesitamos usar la última columna de la tabla como columna de búsqueda. Así que aprobemos BUSCARV aquí. Llamemos INDEX y MATCH para rescatar y la fórmula será:

 =INDEX(G2:G14,MATCH(L6,J2:J14,0),0)

…así es como funciona esta fórmula

• En primer lugar, se refirió a la columna de monto en la función de índice. Esta es la columna de la que necesitamos obtener el valor.
• En segundo lugar , en el argumento número_fila de la función de índice, utilizó la función de coincidencia y especificó el número de factura, hizo referencia a la columna de factura y utilizó cero para la coincidencia exacta.
• En tercer lugar , la función de coincidencia devuelve el número de celda de la factura del rango.

Y al final, ÍNDICE usa este número para devolver el monto colocando la celda de la columna de monto.

3. Búsqueda difusa

Al igual que BUSCARV, también puede utilizar ÍNDICE/COINCIDIR para una búsqueda aproximada.

La búsqueda difusa puede resultar útil cuando el valor que busca no aparece en la lista y desea obtener la coincidencia más cercana. En la siguiente tabla tienes una lista de calificaciones basadas en calificaciones.

Y, si desea obtener una puntuación de 79, puede utilizar la siguiente fórmula.

 =INDEX(B2:B6,MATCH(D3,A2:A6,1))

…así es como funciona esta fórmula

En esta fórmula, hemos utilizado 1 en la función de coincidencia para match_type que le permite realizar una búsqueda aproximada. Devuelve el primer valor que sea menor o igual al valor de búsqueda.

Para 79, el primer valor más bajo es 75, y para 75, la calificación es B. Por eso obtienes una B en el resultado.

4. BÚSQUEDA horizontal

Como sabes, BUSCARH es para búsqueda horizontal, pero también puedes usar ÍNDICE y COINCIDIR para eso. Aquí, en la tabla de datos a continuación, tiene una tabla horizontal para las ventas mensuales y desea obtener el valor de las ventas para «mayo».

Y la fórmula será:

 =INDEX(amount,0,MATCH(lookup_month,months,0))

…así es como funciona esta fórmula

En la fórmula anterior, en lugar de usar COINCIDIR en el argumento núm_fila del índice, lo usamos en núm_columna. Y la coincidencia devuelve el número de columna de mayo.

Y luego ÍNDICE devuelve el valor de la columna de resultado según el número de posición.

5. Búsqueda bidireccional

En una búsqueda bidireccional, necesitamos obtener un valor de una tabla. Solo mira la tabla a continuación donde tienes los montos de ventas por zona y por producto.

Ahora, si desea obtener el monto de ventas de un producto para un área en particular, necesita una búsqueda bidireccional y para eso necesita usar una combinación de INDEX MATCH MATCH. Sí, aquí tienes que usar MATCH dos veces.

En una combinación normal de ÍNDICE y COINCIDIR, usa COINCIDIR para el número de fila, pero en una búsqueda bidireccional también debe usarlo para el número de columna. Las fórmulas serán:

 =INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))

…así es como funciona esta fórmula

En la fórmula anterior, para obtener el monto de ventas de la tabla, usó la función de índice y luego la función de coincidencia para sus argumentos núm_fila y núm_columna. La función de coincidencia, que es el argumento column_num, devuelve 5 como valor del Producto-D en la quinta fila del rango al que hizo referencia.

Y la función de coincidencia que se encuentra en el argumento num_fila devuelve 2 porque el valor de la zona norte está en la segunda columna del rango al que hizo referencia.

Ahora, con estos valores, la función de índice devolvió el valor que está en la segunda columna y la quinta fila: 1456.

6. Distingue entre mayúsculas y minúsculas

Si encuentra un problema cuando tiene dos valores iguales en una lista o columna pero en diferentes mayúsculas y minúsculas, puede realizar una búsqueda que distinga entre mayúsculas y minúsculas para encontrar el valor correcto. Echemos un vistazo a la lista de estudiantes a continuación donde tiene el primer nombre y en la segunda columna ha marcado.

Y, al principio, hay sustantivos que son iguales pero en diferentes casos textuales. Por ejemplo, John Parker y JOHN Mathew. Digamos que desea buscar marcas de «JOHN» y no de «John», puede crear una búsqueda de coincidencia exacta con INDEX y MATCH. Y la fórmula será:

 =INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))

…así es como funciona esta fórmula

Aquí, en esta fórmula, ha utilizado la función EXACTA en la función de coincidencia. Como la función de coincidencia no puede buscar un valor que distinga entre mayúsculas y minúsculas, EXACT es una función perfecta para esto.

Puede comparar dos valores y devolver VERDADERO si son exactamente iguales (incluidos mayúsculas y minúsculas), pero debe ingresar esta fórmula en forma tabular porque necesita comparar toda la columna con un solo valor en EXACTO. Cuando lo ingrese, devolverá una matriz como esta.

 =INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))

A partir de ahí, debe utilizar la función de coincidencia para obtener la posición VERDADERA de la matriz.

En este punto tienes el número de celda (número de fila) del valor que necesitas encontrar. Y puede usar ÍNDICE para el valor de esta columna.

Punto importante: si obtiene más de un VERDADERO con EXACTO, la coincidencia simplemente devolverá el número del primer VERDADERO.

7. Con comodines con INDEX MATCH

Los comodines son muy útiles. Puede realizar una búsqueda parcial utilizando comodines . Y la mejor parte es que, como todas las demás búsquedas de fórmulas, también puedes utilizar comodines con índice y coincidencia.

Simplemente eche un vistazo a la lista de nombres a continuación, donde tiene el nombre y apellido de los empleados y sus edades.

De esta lista necesita obtener la edad de un empleado en particular (Sondra). Pero el caso es que sólo sabes el nombre.

Y, si usa un asterisco, puede buscar la edad de Sondra usando el nombre. Para ello la fórmula será:

 =INDEX(B2:B11,MATCH("Sondra*",A2:A11,0),0)

…así es como funciona esta fórmula

Un asterisco es un carácter comodín que puede reemplazar un número n de caracteres. Entonces, cuando lo usó después del nombre, reemplazó el apellido.

8. Valor más bajo

Digamos que tiene una lista de estudiantes con sus puntajes como se muestra a continuación. Y ahora desde esta lista desea buscar el nombre del estudiante que tiene la puntuación más baja.

Para esto, puedes usar la función MIN con índice y coincidencia y la fórmula será:

 =INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))

Tiene los nombres de los estudiantes en la columna A y sus puntuaciones en la columna B.

Entonces, cuando inserta esta fórmula en una celda y presiona Enter, devolverá el nombre del estudiante con la puntuación más baja, es decir, Librada Bastian.

Explicación

En esta fórmula tenemos tres partes diferenciadas.

En la primera parte, la función MIN devuelve la puntuación más baja.

Después de eso, en la segunda parte, la función de coincidencia devuelve la celda con la puntuación más baja.

Al final, la función de índice devuelve el valor de la columna del nombre del estudiante utilizando la misma posición de celda devuelta por la coincidencia.

Consejo: De la misma forma, también podrás obtener el nombre del alumno que obtiene la puntuación más alta.

9. Mejor puntuación enésima

Ahora piense así, tiene una lista de estudiantes con sus puntajes en los exámenes y de esta lista desea obtener el nombre del estudiante que obtuvo el segundo puntaje más alto.

La cuestión es que no sabes cuál es la segunda puntuación más alta.

Normalmente, cuando buscas un valor con búsquedas de fórmulas, estás seguro del valor que buscas. Pero aquí no se sabe cuál es la segunda puntuación más alta.

Entonces, para esto puedes combinar una función grande con un índice y hacer coincidirlo. La función grande le ayudará a determinar el segundo valor más alto del rango.

Y la fórmula será:

 =INDEX(student_names,MATCH(LARGE(score,2),score,0))

…así es como funciona esta fórmula

En esta fórmula, ha utilizado la función grande dentro de la función de coincidencia para el argumento valor_buscado. Y en la función grande mencionaste el rango de puntuación y 2 para obtener el segundo valor más alto.

Después de que la función grande devuelve el segundo valor más grande, la función de coincidencia usa ese valor y devuelve el número de celda correspondiente.

Y al final, la función de índice usa este número de celda y devuelve el nombre del estudiante.

10. Múltiples criterios

Normalmente la combinación de índice y coincidencia tiene como objetivo buscar un valor único. Y es por eso que usas solo un rango en la función de coincidencia.

Pero, a veces, cuando te enfrentas a datos del mundo real, necesitas utilizar múltiples criterios para encontrar un valor.

Considere el siguiente ejemplo. Aquí tienes una lista de productos con varios detalles como nombre del producto, categoría y tamaño.

Y a partir de estos datos desea obtener el precio de un producto en particular utilizando todos los criterios.

Por tanto la fórmula será:

 =INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0))

Nota: Esta es una fórmula matricial, por lo que debe ingresarla usando Ctrl + Shift + Enter.

…así es como funciona esta fórmula

En esta fórmula tienes tres matrices diferentes para hacer coincidir tres valores diferentes y estas matrices devuelven VERDADERO y FALSO donde los valores coinciden.

Después de eso, cuando los multiplicas entre sí, obtienes una matriz o algo así.

 =INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))

Y la función de coincidencia devuelve la posición 1 para la matriz.

Al final, ÍNDICE devuelve el precio de la columna de precios utilizando el número devuelto por la coincidencia.

Consejo: si no desea utilizar una fórmula matricial, puede utilizar la condición SUMPRODUCT .

11. Primer valor numérico de un rango

Digamos que tiene una lista en la que tiene valores numéricos y de texto, y ahora de esta lista desea obtener el primer valor numérico.

Para hacer esto, puede combinar la función ESNUMERO con índice/coincidencia. ISNUMBER puede ayudarle a identificar qué valor es un número y cuál es texto.

La fórmula será:

 =INDEX(list,MATCH(TRUE,ISNUMBER(list),0))

Debe ingresar esta fórmula en forma de tabla (usando Ctrl + Shift + Enter).

…así es como funciona esta fórmula

En esta fórmula ESNÚMERO devuelve un array igual al largo de la lista y en este array tienes VERDADERO para los valores que son números y FALSO para el resto de valores.

Después de eso, usó VERDADERO en la función de coincidencia como valor de búsqueda. Entonces devuelve el número de posición del primer VERDADERO en la matriz.

En última instancia, el uso de este índice de número de posición devuelve el primer valor numérico.

12. Obtenga el primer valor que no esté vacío.

Pensemos así: tiene una lista de valores en la que algunas de las primeras celdas están vacías y desea obtener el primer valor que no esté vacío.

Y puede utilizar esta fórmula para obtener este primer valor no vacío.

 =INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0))

…así es como funciona esta fórmula

Necesitamos dividir esta fórmula en tres partes diferentes para entenderla sabiamente.

Primero , usó la función ESBLANCO en la función de coincidencia para obtener una matriz donde tiene VERDADERO para celdas vacías y FALSO para celdas no vacías.

En segundo lugar , COINCIDIR devuelve el número de posición del primer VERDADERO en la matriz devuelta por ISBLANK.

Entonces, en este punto tiene el número de celda del primer valor que no está en blanco.

En tercer lugar , la función de índice simplemente devuelve el primer valor no vacío de la lista.

13. Texto más frecuente

Ahora supongamos que, dada una lista de valores de texto, necesita contar el texto más frecuente.

En la lista de abajo tienes nombres.

Pero hay algunos nombres que aparecen más de una vez.

Entonces, ahora necesita obtener el nombre que tiene la mayor aparición en la lista. Puede utilizar la siguiente fórmula, que es una combinación de MODO, ÍNDICE y COINCIDENCIA.

 =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))

Cuando insertes esta fórmula, devolverá «Tamesha», que es el nombre más común.

…así es como funciona esta fórmula

Primero , MATCH comparará todo el rango de nombres consigo mismo. Y, al hacerlo, devolverá una matriz donde cada texto representará su primera posición.

Tomemos un ejemplo del nombre «Tamesha», que es nuestro nombre más común en la lista. Ahora, si miras la lista, esto sucedió primero en la celda 8 y luego en la celda 12.

Pero, si miras la tabla, para todas las posiciones donde tenemos a «Tamesha», devolvió 8 que es su primera posición.

Después de eso, de la matriz devuelta por MATCH, la función de modo devolverá el número más frecuente, que es el número de celda de la primera aparición de » Tamesha «.

Y al final, INDEX devolverá el texto usando este número de celular.

14. Crea un hipervínculo

Ahora, digamos que además de buscar un valor, también desea crear un hipervínculo para ese valor. De esta manera podrás navegar rápidamente a la celda donde se encuentra tu columna de búsqueda.

Por ejemplo, en la siguiente tabla necesitas obtener la edad de una persona. Y, si crea un hipervínculo para este valor, puede navegar fácilmente a la celda donde se encuentra este valor.

Y, para ello debemos usar HIPERVÍNCULO + Celda con ÍNDICE y COINCIDIR y la fórmula será:

Así funciona esta fórmula

Separemos esta fórmula en varias partes para entenderla mejor.

• En primer lugar, usaste índice y coincidencia en la función de celda. Y, cuando usa ambas funciones en la función de celda, obtiene una referencia de celda del valor correspondiente en lugar del valor correspondiente.
• En segundo lugar , ha concatenado «#» con la referencia de celda.
• En tercer lugar , utilizó index y match nuevamente para obtener el valor coincidente para usarlo como texto del enlace. De esta forma tienes el valor correspondiente así como el enlace a la celda donde se encuentra ese valor.