Usar INDICE y COINCIDIR en Excel en lugar de BUSCARV

viernes, junio 09, 2006

No se cómo se me pasó de publicar esta entrada sobre el uso de INDICE y COINCIDIR, que publiqué en mi blog en inglés, en enero. Así que disculpas, y aquí va.
Cuando usamos BUSCARV, la función supone que el valor buscado se encuentra a la izquierda de la matriz de búsqueda. Por ejemplo, si en esta tabla buscamos quien es el cliente al cual le hemos enviado la factura no. 9459, no podemos usar BUSCARV.




Tendríamos que usar como indicador de columnas (el tercer argumento de la función) el número -1. El valor de este argumento no puede ser menor que 1.
La solución es usar una fórmula que combina las funciones INDICE y COINCIDIR de la siguiente manera

INDICE($B$4:$B$13,COINCIDIR(A17,$C4:$C$13,0))

Y si utilizamos

nombres para los rangos

cliente =Hoja1!$B$4:$B$13
factura =Hoja1!$C$4:$C$13

INDICE(cliente,COINCIDIR(A17,factura,0))

Podemos aprovechar los rangos que acabamos de nominar, para general una lista desplegable con Validación de Datos en la celda A17 que será argumento para nuestra fórmula, que anotamos en la celda B17 (el cuaderno con el ejemplo se puede ind_coinc_spdescargar aquí)

Desagraciadamente hace varios días que la interfaz de Blogger para subir imágenes no funciona. Así que recommiendo descargar el archivo para ver la solución la implementación de validación de datos.



Categorías: Funciones&Formulas_

Technorati Tags: ,





Categorías: Funciones&Formulas_

Technorati Tags: , ,

50 comments:

francisco,  03 julio, 2006 02:03  

me ha parecido muy interesante este blog y me ha resuelto varios problemillas que tenía, no obstante me gustaría poder hacerle a Ud. alguna pregunta sobre nuevas dudas que tengo, podría indicarme Ud. si desde aquí le puedo enviar mi pregunta.
Atentamente,
Francisco

Jorge L. Dunkelman 03 julio, 2006 17:28  

Hola, estás invitado a dejar tus comentarios y tus preguntas.
También puedes comunicarte via e-mail.

Pseudo Blog 06 diciembre, 2006 15:56  

Extraordinario, me has solucionado un problema, ya que tenía que volver a crear planillas para usar buscarV, ahora con INDICE y COINCIDIR, se simplifica mucho el tema.
Gracias.

Carlos Valenzuela C.

Rosanna 26 julio, 2007 18:32  

No he podido abrir el archivo

Jorge L. Dunkelman 27 julio, 2007 00:23  

Hola Rossana

he corregido el enlace. Puedes descargar el archivo.

Bersain,  18 octubre, 2007 19:47  

Gracias por compartir tus conocimientos. El blog es excelente y me gustaria saber a qué dirección de correo electrónico se te puede contactar, no obstante, describo mi problema:

Tengo una hoja con detalles de presupuestos los campos esenciales son NUMERO DE PRESUPUESTO y MODULO, si quiero realizar la coincidencia considerando dos valores posibles que coincidan Número de Presupuesto y Módulo, la fórmula que mas o menos da con el valor buscado es:

=SI(Y($A$4=INDICE('Detalle Presupuesto'!$A:$E,COINCIDIR($A$4,'Detalle Presupuesto'!$A:$A,0)+FILA()-FILA($A$4),1),$B$4=INDICE('Detalle Presupuesto'!$B:$E,COINCIDIR($B$4,'Detalle Presupuesto'!$B:$B,0)+FILA()-FILA($B$4),1)),INDICE('Detalle Presupuesto'!$A:$E,COINCIDIR($A$4,'Detalle Presupuesto'!$A:$A,0)+FILA()-FILA($A$4),3),"")

esta fórmula está en la celda C4 y la copio en en las celdas C5, C6, ... CN, considerando que puedo tener cierta cantidad de valores con el numero de presupueso y modulo.

Luego mi intencion es hacer las busquedas con otro módulo, pero en las celdas E4, E5, ... EN (en D4 pongo el valor del modulo que debe coincidir)

Realmente me gustaria saber qué es lo que estoy haciendo mal o mejor aun, que me pudieras dar una solución mejor y más eficiente a mi problema. De antemano te agradezco encarecidamente el tiempo por la lectura.

Jorge L. Dunkelman 18 octubre, 2007 20:25  

Hola Bersain

puedes mandarme el archivo a la dirección jorgedun@gmail.com

Albertinho 02 abril, 2008 15:34  

Hola Jorge, Mi duda es la siguiente. Tengo una tabla salida de una encuesta en la que por un lado tengo las respuestas segmentos (por ejemplo edad con 5 respuestas posibles) y las respuestas a las preguntas (en 5 numeros posibles)
Me gustaría que en otra hoja pudieran aparecer seguidos, todos aquellos que por ejemplo tienen entre 30 y 39 años(uno de los segmentos de edad)y abajo todas sus respuestas a la primera pregunta.
Si lo hago con buscarv, es decir busca en esta columna los de esta edad y dame su respuesta, solo me da la del primero. Como puedo hacer para que en la segunda fila me de el siguiente cuestionario en que ha respondido alguien de 30 a 39 años?
La intuicion es que quiero que todos los resultados que en un sitio me salgan salteados en el otro me salgan seguidos.
Si prefieres te puedo mandar el archivo para que te hagas una idea
Muchisimas gracias

Jorge L. Dunkelman 02 abril, 2008 21:06  

ALbertinho
si, mandame el archivo. La consulta excede el marco de un comentario.

CARLOS 08 setiembre, 2008 18:56  

Buenos días Sr. Dulkenman, le escribo por una gran duda que tengo con una fórmula de excel, y este es el único sitio en donde he encontrado gran información para aclarar mis dudas.

La cuestión es: Estoy entrenando para una prueba física de natación, y he encontrado en la web una tabla que me permite llevar mis registros de ingesta calórica. Esta tabla arroja resultados automáticos de las calorías a medida que introduzco los gramos de alimentos. Quisiera saber como hago para elaborar otra tabla como esa pero para usarla en mis progresos de ejercicios, asi me ahorraría tener que sumar manualmente y todo sería más fácil.

De antemano Gracias, y felicitaciones por tan instructivo el blog.

Jorge L. Dunkelman 08 setiembre, 2008 20:40  

Hola Carlos
este tipo de consultas tienes que dirigirla a mi mail *el que aparece en la columna izquierda del blog, arriba). Lo mejor es mandar un archivo con el ejempl y la consulta.

Manel 19 noviembre, 2008 17:13  

Apreciado Jorge:
Ante todo, felicidades por el Blog. Me suscribo a las peticiones de publicacione de un libro por tu parte.
Mi comentario es el siguiente: tomando el archivo anexo como referencia, me he tomado la libertad de alterar el orden de las celdas A16:B17, es decir donde ponía Fra. ahora pone Cliente y viceversa. He modificado la fórmula y la validación de datos.
Hasta aquí todo correcto.

Ahora bien: si duplicamos uno de los clientes (por ej. en la fila B5 "Cliente 42", en la validación de datos salen 2 (correcto), pero sólo devuelve el primer número de factura.

¿Es posible hacer que aparezcan todos los núm. de factura?

Atentamente

Jorge L. Dunkelman 21 noviembre, 2008 08:24  

Hola Manel

en una celda se puede mostraar sólo un resultado por vez, por lo que una fórmula no puede mostrarte más de un resultado.
Para el caso que describes hay varias alternativas:

# usar tablas dinámicas (la alternativa más eficiente en mi opinión)

# usar una macro que cree una lista de las coincidencias y ponga los resultados en algún rango de la hoja

# crear poner en un rango de la hoja una tabla donde en la columna de la izquierda hay un rango de números del 1 al 10 (suponiendo que 10 sea el máximo posible de resultados) y en la columna adyacente poner una de las fórmulas que muestro en la nota sobre BSUCARV con valores repetidos

# hacer lo mismo pero usando una función UDF (definida por el usuario)

Anónimo,  23 noviembre, 2008 18:43  

felicidades por su blog, es de gran ayuda contra el no siempre amigo Excel.

jadiel 09 diciembre, 2008 22:45  
Este blog ha sido eliminado por un administrador de blog.
Anónimo,  20 setiembre, 2010 15:07  

Hola Jorge:

tengo algunas fórmulas en excel, para gestionar una liga de fútbol. Tengo un problema para saber como puedo mostrar en la hoja 1 de un libro, los datos que aparecen en otra hoja de otro libro (digamos, la base de donde salen esos datos) siempre y cuando coincidan algunas condiciones.

Me explico.

En el libro "liga1" tengo una hoja llamada "partido" donde tengo los datos de cada partido de liga y unas celdas dedicadas a mostrar el historial sobre ese partido, o sea, las veces que se ha celebrado ese mismo partido y las veces que ha ganado el local (1), han empatado (x) o ganado el visitante (2).

El problema es que no sé como componer la fórmula para que en esas celdas se muestren los datos que tengo en una tabla en otra hoja, donde en la primera columna tengo a los equipos y en la segunda al contrario.

He probado con buscarv, intentando buscar el contenido de la celda del equipo local en el partido para que buscara la coincidencia con el equipo de la primera columna en el otro libro pero nada. Y también he probado mediante el =si(...) pero creo que esta no me sirve, porque sería más concreta con la celda a mostrar y yo necesito que busque en un rango y muestre los datos si coincide con las 2 condiciones que le digo (1.- que el euqipo local coincida con el local de la tabla, 2.- que el equipo visitante coincida con el de la tabla).

Gracias de antemano.

fran,  22 setiembre, 2010 04:55  

Hola
tengo una duda...Quiero utilizar index y match para una lista de nombres y puntajes, de modo de encontrar los 3 puntajes mas altos. (usando large o kesimo mayor) Mi problema es que se repiten los puntajes maximos, (el primero y segundo mayores por ejemplo) y al usar la funcion indice, me entrega solo uno de los nombres. Por ejemplo si juan y pedro tienen los maximos puntajes, entonces para el primer lugar entrega juan y para el segundo tambien, a pesar de que deberia dar pedro en alguno de los lugares.
Que puedo hacer??
Muy bueno el blog

Jorge L. Dunkelman 23 setiembre, 2010 08:20  

Hola Fran,

en esta nota muestro una técnica para calcular una tabla sin empates.

Anónimo,  28 diciembre, 2011 18:24  

Hola buen dia
necesito encontrar una formula que me de por resultado la celda en la que coincide un fila con una columna, por ejemplo tengo los nombres de los empleados en la columna A en b1 enero c1 febrero y asi todos los meses la tabla indica el nombre del empleado y los dias de falta por mes en numero, necesito colocar una formula que busque automaticamente el numero de faltas si tomo en cuenta el nombre del empleado y el mes del año
he intentado con formulad de buscar e indice pero no coincide
que puedo hacer

Jorge L. Dunkelman 28 diciembre, 2011 21:08  

Bueno, eso es exactmente lo que hace INDICE combinado con COINCIDIR, por lo que supongo que tienes algún problema en la forma de construir la fórmula.
Puedes enviarme el archivo con el problema para que pueda hacerme una idea más cabal.

Flavio 22 marzo, 2012 16:32  

tengo un problema cuando el numero de registros excede en la matriz de partida a la matriz que se busca y ya no funciona buscarv cual seria la solucion .

gracias

Jorge L. Dunkelman 23 marzo, 2012 19:14  

Flavio,
no me queda claro a que te refieres con "matriz de partida". En BUSCARV hay un valor de búsqueda y una matriz de búsqueda.

Gerardo 09 julio, 2012 17:16  

Hola soy gerardo seguidor de jorge desde que desubri este maravilloso blog, desde entonces he avanzado muchisimo en mis conocimientos de excel superandome cada dia.
la solucion en este caso seria una funcion personalizada donde uno de los parametros sea la columna de la matriz donde esta valorbuscado y otro sea la columna de la matriz donde esta valordeseado sin importar si esta a la derecha o no. saludos

Gabriel Gonzalez 16 julio, 2012 03:15  

hola, si tengo una base de clientes y quiero hacer una encuesta, utilizo las funciones indice, coincidir y kesimo. el problema es que todos los dias agrego nuevos clietes y al ejecutar la formula, me repiye algunos de los clientes. ¿Como lo evito?

Jorge L. Dunkelman 16 julio, 2012 07:01  

Puedes usar algunas de las técnicas que muestro en esta nota para generar números aleatorios únicos.

Jose Riu,  17 setiembre, 2012 19:53  

Hola Jorge. perdon si n ocorresponde que te haga una consulta en este blog tan viejo; pero me encontre con un problema tratando de utilizar COINCIDIR.
en ninguna parte indican que el rango de celdas donde se encuentran los datos a buscar, solo puede corresponder a una fila o una columna, no permitiendo la busqueda en matrices bidimensionales.
Por favor corregime si no estoy en lo correcto, pero el siguiente ejemplo de busqueda daria error:
=coincidir(A1;C1:B10;0).
de no ser posible esta modalidad, te solicito por favor me indiques de que forma puedo buscar un elemento en una matriz de mas de 1 fila y mas de 1 columna.
desde ya , muchas gracias.

Jorge L. Dunkelman 18 setiembre, 2012 20:01  

Hola José

si bien no lo he visto documentado en la base de conocimientos de Microsoft, se por experiencia y por lo que se ha publicado en otros foros, que COINCIDIR funciona solamente con rangos de búsqueda de una columna (o fila). Fijate si la técnica que muestro en esta nota te sirve.

Jorge L. Dunkelman 18 setiembre, 2012 20:25  

José

me quedé pensando en tu consulta y en tu caso en particular, si usas Excel 2007 o 2010 podrías usar esta fórmula

=SI.ERROR(COINCIDIR(A1;B1:B10;0);SI.ERROR(COINCIDIR(A1;C1:C10;0);0))

Esta fórmula te da el número de orden de la primer coincidencia.

La versión para Excel 97-2003 sería

=SI(ESERROR(COINCIDIR(A1;B1:B10;0));COINCIDIR(A1;C1:C10;0);COINCIDIR(A1;B1:B10;0))

Jose Riu,  26 setiembre, 2012 16:50  

No me llego el mail avisando de tu respuesta. Aunque sea tarde, las gracias siempre hay que darlas. lo unico "incomodo" en esta solucion, es que la dimension de la matriz, debe conocerse de antemano (especificamente la cantidad de columnas) y no siempre es asi. de todas formas, muchas gracias por tu dedicacion.abrazo.

Jorge L. Dunkelman 04 octubre, 2012 17:30  

Hola José,

acabo de volver de mis vacaciones y veo tu comentario. La solución es usar rangos dinámicos. Haz una búsqueda en el blog con el valor "rangos dinámicos" para ver las notas que tratan sobre el tema.

Anónimo,  12 febrero, 2013 00:41  

Hola Jorge, mi nombre es Rodrigo Hdez y tengo un problema que quiero resolver con tu ayuda:
Resulta que tengo 3 hojas de excel (2010, 2011, 2012) tengo en las filas los productos (1,000) de la empresa y en las columnas los meses (ene-dic) en las celdas viene las ventas ocasionadas mensualmente de cada producto, requiero saber si algunos de los productos repiteron exactamente su mismo nivel de venta en todos los meses ya sea dentro del propio 2010 o en el 2011 o 2012.
Mil gracias y saludos

Jorge L. Dunkelman 13 febrero, 2013 19:47  

Hola Rodrigo,
tendrías que empezar por organizar tus datos de manera que puedas empleae tablas dinámicas (la herramienta más eficiente de Excel).
Tendrías que poner los datos en una única tabla (no veo por qué separar por años). Esta tabla (matriz) tendría esta columna (campos): producto, ventas (o el dato que manejes), mes y años. En la hoja de la tabla no se hacen cálculos, sólo datos. En otra hoja creas una tabla dinámica con los productos en el área de las filas y el año y mes en el área de las columnas. De esta manera podrías ver con facilidad (por ejemplo con formato condicional) si un número se repite en la fila.

Sergio Vásquez 04 marzo, 2013 19:09  

Jorge, felicitaciones por tu blog, se ha constituído en una herramienta indispensable.

Mi consulta, ¿cómo utilizo INDICE y COINCIDIR cuando los datos que busco están en una planilla distinta a la que estoy trabajando con la fórmula?

De antemano muchas gracias..!!!!

Atte.;

Sergio Vásquez

Sergio Vásquez 04 marzo, 2013 19:19  

...Jorge, omití indicar en mi consulta cuando se usan nombres para los rangos, de hecho no tengo problemas para usar la formula usando los rangos directamente haciendo referencia a otra planilla....

Jorge L. Dunkelman 05 marzo, 2013 07:00  

No hay diferencias entre usar referencias explícitas o nombres en las fórmulas. Excepto que con "plantillas" te refieras a cuadernos distintos y no a hojas dentro de un mismo cuaderno.

Anónimo,  08 mayo, 2013 17:15  

Viejo blog y administrador atento.

Felicidades

José Luis,  18 junio, 2013 07:43  

Estimado, encuentro excelente tu aporte con excel.

Te quería hacer una pregunta que estoy haciendo una tabla y no se como solucionar un error.

Resulta que tengo una tabla que en la columna A aparece el nombre de la personas y en la columna B el tiempo de una prueba de velocidad. Necesito que en otras celdas se me ordenen automáticamente del peor al mejor tiempo con sus respectivos nombres, hasta este momento voy bien. En la columna C usé la formula K.ESIMO.MENOR y en la columna D usé INDICE y COINCIDIR pero resulta que cuando los tiempo son iguales solo pone el nombre de la primera persona con ese tiempo y la segunda no aparece por ninguna parte.

Hay alguna forma de solucionar eso para que todos los nombres aparezcan aún cuando estén con tiempos iguales?

Muchísimas gracias!

Jorge Dunkelman 18 junio, 2013 22:21  

Hola José Luis

se puede solucionar usando la función JERARQUIA con alguna de las técnicas que muestro en esta nota.

Anónimo,  13 octubre, 2013 12:48  

¡Hola, Jorge! En ese mismo ejemplo ¿Como podríamos obtener una lista de las facturas que se le han mandado a un determinado cliente entre unas fechas concretas? Suponemos que hay clientes a los que se les ha mandado varias facturas. Saludos: Lohe.

Jorge Dunkelman 13 octubre, 2013 17:55  

No con f'ormulas. Las fórmulas sólo pueden dar un resultado por celda. La mejor herramienta para lo que quieres hacer es usar una tabla dinámica.

Fede 12 mayo, 2014 17:39  

Buenas, tu blog es muy interesante, he aprendido varias cosas ya, pero tengo una duda:
En el caso que uno (en el mismo ejemplo que das) quiere buscar la ultima factura de un cliente (suponiendo que estos repiten mas de una vez) ¿con VLOOKUP se puede lograr esto? gracias de antemano

Anónimo,  11 noviembre, 2014 18:45  

hola, tengo una duda. Cómo se saca por ejemplo de dos columnas: una de alumnos y otra con las notas finales, la 2ª peor nota con el formato condicional, y que salga sombreado el nombre del alumno? Me pide las 2 peores notas.
Sé que la fórmula es:
=indice(rango que quieres que salga sombreado);coincidir(min(rango de las notas);(rango de las notas);0)). esa es para la peor nota.
Pero cuando voy a hacer la siguiente no me sale, sé hay que poner un 1, en donde está el cero, pero no me sale de ninguna manera. No sé dónde me equivoco.

Gracias de antemano y enhorabuena por el blog.

Jorge Dunkelman 12 noviembre, 2014 08:12  

Te sugiero usar la función K.ESIMO.MENOR. Por ejemplo, si las notas estan en el rango B2:B30, para extraer la segunda peor nota usamos =K.ESIMO.MENOR(B2:B30,2). La misma fórmula usamos para el formato condicional.

Anónimo,  12 noviembre, 2014 21:29  

Hola Jorge, he entendido bien lo que quieres decir. He probado la fórmula y me sale. Pero no es eso lo que quiero. Me pide por fuerza SOMBREAR el NOMBRE del ALUMNO:

ana..........2,94
juan.........4,80
pepe........5,70
maría.......7,20
paco........3,15
carmen.....8

Supongo que tiene que salir como te dije. Con ÍNDICE y COINCIDIR, no?

Gracias.

Jorge Dunkelman 13 noviembre, 2014 07:45  

No, ninguna necesidad de usar INDICE y COINCIDIR. Siguiendo con tu ejemplo, supongamos que los nombres están en el rango A2:A7 y las notas en B2:B7; los pasos a dar son:

1 - seleccionamos el rango A2:A7 (el de los nombres)
2 - abrimos el menu de Formato Condicional y en la opción Fórmula ponemos ésta
=K.ESIMO.MENOR($B$2:$B$7,2)=B2

Fijate que tenemos seleccionado el rango de los nombres pero la fórmula se aplica al rango de las notas. Es decir, la fórmula que usamos en formato condicional no debe referirse necesariamente al rango donde aplicamos el formato.

Anónimo,  13 noviembre, 2014 13:39  

Hola, tengo dos excell , quiero cruzar los datos para ver los que me coinciden en una tabla y otra y que me dé el valor de una de las columnas de una de las tablas. Pongo: Indice(columna que quiero que me de el valor de la hoja 1;coincidir celda hoja 1;columna hoja2;0)) y no me sale. ¿Qué hago mal?

Anónimo,  14 noviembre, 2014 18:30  

Dudas resueltas. Gracias!

Jorge Dunkelman 15 noviembre, 2014 18:02  

No se decirte porque no entiendo el planteo. ¿Podrías explicar un poco más que quiers decir con "quiero cruzar los datos para ver los que me coinciden en una tabla y otra y que me dé el valor de una de las columnas de una de las tablas"?

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP