Usar Excel BUSCAR (LOOKUP) para encontrar el último valor en un rango

domingo, abril 09, 2006

Usar Excel BUSCAR (LOOKUP) para encontrar el último valor en un rango

Últimamente he estado participando en el foro Excel de
http://www.exceluciones.com

Una de las preguntas que han surgido allí, y que aparecen a menudo en varios foros, es qué fórmula nos permitiría encontrar el último valor de un rango. La idea es que tenemos, por ejemplo, un rango en una columna donde vamos agregando valores, por ejemplo el rango A1:A20. Si agregamos un valor en la celda A21, queremos una fórmula que de cómo resultado el valor de A21.
La solución "clásica" es utilizar la función BUSCAR (LOOKUP), poniendo como argumento el número más elevado que Excel acepta en una celda (al valor 1E+307) y como segundo argumento el rango donde debemos encontrar el último valor (hay que prestar atención que estamos buscando el último valor en el sentido de orden y no de tamaño).
El funcionamiento de la función BUSCAR (LOOKUP) es tal que si el valor buscado es mas alto que cualquier valor disponible en el rango de la búsqueda esta función da como resultado el ultimo valor que encuentren.




En este lookup_sp_01ejemplo vemos que cuando el valor del primer argumento (1000) es mayor que el valor del último valor en el rango (172), la fórmula da como resultado 172. Si el valor del primer argumento de BUSCAR (LOOKUP) es, por ejemplo 126, el resultado de la fórmula será 118.





LOOKUPS_


Technorati Tags: ,

47 comments:

Jaizki 09 junio, 2007 15:07  

Si todas las celdas del rango de números tienen contenido y A1 y A2 también, en el caso del ejemplo se me ocurre que otra opción sería:

=INDIRECTO("A" & CONTARA(A:A))

En caso de que A1 y A2 no tuviesen contenido, habría que sumar 2 al resultado de CONTARA.

Jorge L. Dunkelman 09 junio, 2007 16:10  

Hola Jaizki
buen aporte. El problema es que no siempre sabes cuantas celdas en blanco puedan haber en el rango.

byfed,  22 junio, 2007 01:39  

Pero siempre puedes utilizar la funcion contar.blanco

luis,  05 septiembre, 2007 20:15  

necesito usar lookup como una especie de IF porque IF solo deja colocar 7 condiciones y lei en la ayuda de excel que se tenia que usar look up. el problema es que tengo que poner si un numero en una celda esta entre 0 y 15 en otra celda tiene que salir automaticamente 25, si esta entre 16 y 30 tiene que salir automaticamente 50, y asi hasta llegar a las 13 condiciones. como puedo hacer esto? muy agradecido
luis

Anónimo,  14 octubre, 2008 13:23  

¿Por qué no: max(A:A)?

Jorge L. Dunkelman 14 octubre, 2008 22:01  

Porque el máximo valor en el ragno no es necesariamente el último en el rango. En el ejemplo de la nota ambos valores coinciden porque la lista está ordenada. Pero si mueves el 172 a otra posición en la lista, la fórmula dará 163 que ha pasado a ser el último valor en el ranog. La función MAX siembre dará 172.

alfpma 29 diciembre, 2008 16:27  

Sr. Dulkeman deseo que este año 2009 para ustede y toda su familia sea el mejor, que Dios lo nutra de muchos mas conocimiento y sobre todo lo siga bendiciendo con esa manera humilde y desinteresada con que los comparte.....que tenga una FELIZ NAVIDAD Y UN GRAN AÑO 2009.

Su amigo....Alfonso Córdoba - Panamá

Anónimo,  22 febrero, 2010 04:46  

=INDIRECTO("B" &CONTAR(B:B)+3) es facil

Jorge L. Dunkelman 22 febrero, 2010 06:34  

El problema con esa solución, que ya había propuesto Jaizki en su comentario, es que CONTAR no toma en cuenta las celdas en blanco. Si hubiera alguna celda en blanco, el resultado sería incorrecto. Dado que estás usando todo el rango de la columna, no podemos usar CONTAR.BLANCOS para corregir el resultado.

Henry,  07 diciembre, 2010 17:22  

Buenos días. Tengo una columna en la cual algunas celdas contienen la letra X, significa que hay celdas en blanco intercaladas. No he logrado encontrar la forma (usando funciones), de ubicar la última X de esa columna. Agradeceré mucho si me pueden ayudar.

Jorge L. Dunkelman 08 diciembre, 2010 11:20  

Henry,
¿podrías ser un poco más explícito? Con ubicar, ¿te refieres a determinar la dirección de la celda? La celda, ¿contiene sólo la letra X, o un texto que contiene la letra X?

Henry,  08 diciembre, 2010 16:17  

Jorge, gracias por su amable atención.
El caso es: Dentro de la columna D de una hoja, algunas celdas contienen solamente una letra X (NO un texto conteniendo X), las demás celdas están vacías (intercaladas). Pretendo obtener la dirección/referencia de la última celda de esa columna que contenga X.
Buscando más a fondo en su blog encontré una solución "casi completa":
DIRECCION(SUMAPRODUCTO(MAX((FILA('Oct-10'!D5:D35)*('Oct-10'!D5:D35<>""))));1).
La formula citada me funciona parcialmente aunque dados mis escasos conocimientos no tengo ni idea de como lo hace.
Digo "parcialmente" dado que obtengo como respuesta $A$34 pero no $D$34 que es el dato exacto buscado.

Jorge L. Dunkelman 09 diciembre, 2010 19:31  

Hola Henry,

todo lo que hay que hacer es reemplazar el 1 en

DIRECCION(SUMAPRODUCTO(MAX((FILA('Oct-10'!D5:D35)*('Oct-10'!D5:D35<>""))));4)

por 4 (1 = columna A; 4 = columna D)

Henry,  10 diciembre, 2010 03:16  

Jorge, perfecto!!!, me funcionó tal como deseo!!!
Muchas gracias por dedicarme su tiempo ayudándome a encontrar la solución.
Cordial saludo.

Henry,  10 diciembre, 2010 13:17  

Hola, solicito su ayuda nuevamente.
Me surgió un problema, le podría enviar mi libro a su correo?

Jorge L. Dunkelman 10 diciembre, 2010 17:02  

Si, fijate en el enlace Ayuda (en la parte superior del blog)

j. de borja 29 febrero, 2012 03:06  

como hacer para que el valor que me sea el primero por encima (en el ejemplo, que con el valor de 126 el resultado sea 127 y no 118?)

Jorge L. Dunkelman 29 febrero, 2012 18:58  

Si puedes ordenar la lista en orden descendente (de mayot a menor), puedes usar esta fórmula:

=INDICE(A4:A23;COINCIDIR(126;A4:A23;-1))

Si la lista no esta ordenado, la respuesta es más compleja e irá en una futura nota.

Jorge L. Dunkelman 29 febrero, 2012 21:11  

Me acabo de acordar que traté en tema en esta nota

Anónimo,  11 marzo, 2012 04:26  

Buen día,Tengo una columna, donde hay celdas con valores numéricos y las otras celdas marcadas con "-". Necesito encontrar la celda con el ultimo "-" dentro de un rango que yo especifique, lo he hecho con la función =BUSCAR("-";$G$8:G14;$I$8:I14), donde es el rango donde buscar y $I$8:I14 es el rango de celdas con los valores que quiero tener de vuelta, pero no me sirve del todo, aveces no da el ultimo "-".

Jorge L. Dunkelman 13 marzo, 2012 20:04  

BUSCAR siempre da el mayor valor del vector de resultado. Por eso, a veces coincide con el valor esperado y a veces no.
Una alternativa es usar esta UDF (función definida por el usuario):


Function find_last_value(Valor_Buscado As Variant, _
Vector_de_Comparacion As Range, _
Vector_de_Resultado As Range) As String

Dim lLastPosinRange As Long, iX As Long

lLastPosinRange = Vector_de_Comparacion.Count

For iX = lLastPosinRange To 1 Step -1
If Vector_de_Comparacion(iX) = Valor_Buscado Then
find_last_value = Vector_de_Resultado(iX)
Exit Function
End If
Next iX

End Function

Puedes copiar este código en un módulo del cuaderno Personal o en el cuaderno donde quieres usar la función. La función aparecerá en el asistente de funciones bajo la categoría "Definiodas por el usuario".

Anónimo,  28 mayo, 2012 19:55  

Buenas tarde mi consulta es la siguiente
Nº FECHA
128830 02/12/2011
128831 02/12/2011
128832 02/12/2011
128833 03/12/2011
128834 03/12/2011
128835 03/12/2011
como puedo llegar a esto
128830 02/12/2011
128833 03/12/2011

maria 14 diciembre, 2012 13:44  

Hola, ¿y si tengo texto en lugar de números?
Muchas gracias

Jorge L. Dunkelman 23 diciembre, 2012 16:54  

=BUSCAR("zzzzz", rango de búsqueda)

ricardo m 25 enero, 2013 20:04  

que tal Jorge L. Dunkelman, veo que tus conocimientos en excel son amplios. mi nombre es ricardo muñoz, y tengo unas 2 dudas de nivel avanzado que no puedo resolver:

1. tengo una formula para buscar el ultimo valor que sea mayor a "cero":

=BUSCAR(1,(1/(C3:C8>0)),C3:C8)

y jala bien, el detalle es que lo quiero utilizar para que me traiga el ultimo numero de diferentes rangos en una misma columna, es decir, que me traiga el ultimo valor mayor a "cero" de los rangos C3:C8 C10:C20 y C24:230

no hago un solo rango C3:C30 por que entre esos rangos tengo titulos en texto y si en los ultimos rangos no tengo nada me trae el ultimo texto!

espero me puedas apoyar!


2. en una base de datos estoy poniendo 4 listas desplegables que quiero que me funcionen de la siguiente forma: la primer lista desplegable (llamemosle Nivel 1), si me seleccionan el primer valor, que para la siguiente lista desplegable (nivel 2), solo me ponga los valores de nivel 2 que corresponden al nivel 1 seleccionado, y para la siguiente lista despleglable (nivel 3), solo me enliste los valores que corresponden al valor seleccionado en el nivel 2.

mejor dicho, la primer lista sería de estados de la republica, si me seleccionan el edo de michoacan, que para la siguiente lista de nivel 2 me ponga solo los municipios del estado de michoacan, y si en ese nivel 2 seleccionan el municipio de morelia, que en la siguiente lista desplegable me ponga solo las colonias del municipio de morelia y asi consecutivamente.

Esto ya lo logre con algunas celdas de apoyo. es decir, los valores de las listas desplegables estan en blanco, si el valor seleccionado de la lista nivel 1 es "michoacan" en un indice con la formula buscarv me trae un numero indicador, por ejemplo: se escogio michoacan y este es el valor 16 y entonces me trae el numero 16 en la celda contigua, ahora para pintar los valores de la segunda lista, en cada celda vacia pongo la formula: si el valor que trajo es=16 entonces busca y traeme el valor que corresponde a los municipios de michoacan y asi la lista desplegable se pinta con los valores que le corresponden a michoacan, y asi consecutivamente con los otros niveles.

"espero no heberte hecho bolas" jeje

el problema es cuando esa herramienta la quiero utilizar en una base de datos, por que al copiar la formula a las celdas hacia abajo, ya no aplica mi listado que me pinta los valores segun el valor seleccionado en nivel 1, por que yo le digo que me pinte la lista segun el valor que traiga, pero solamente tomará el valor de la primer fila y no el de la fila que le corresponda por ejemplo si estoy en la fila 5 o 30 etc. sigue considerando el numero indicador de la fila 1

sabes si hay alguna formula para simplificar, corregir o mejorar este tema, o algun macro?

saludos y gracias

Jorge L. Dunkelman 26 enero, 2013 10:18  

En lo que hace a tu primer consulta, la fórmula a usar es

=BUSCAR(2,1/ESNUMERO(C3:C30),C3:C30)

Esta da como resultado el último valor numérico, no importa que en el rango haya celdas vacías o que la última contenga texto.

En cuanto a la segunda consulta, la forma de hacer lista desplegables dependientes es usando INDIRECTO junto con nombres definidos. Fijate en las notas sobre listas deslegables dependientes publicadas en el blog (puedes acceder a todas pulsando el enlace correspondiente en la nube de etiquetas).

juan fernández 05 febrero, 2013 02:18  

Hola Jorge.
Tenía un problema similar al del anónimo que te consultó el 11 marzo de 2012. Probé con la UDF que propusiste y me sirvió. Con una salvedad. Devuelve los valores correctamente pero no puedo aplicarles formato. En mi caso, los valores son fechas y cifras que refieren cantidades. Por ejemplo, tengo una celda con el texto "53,86", quiero aplicarle el formato Moneda, o Contabilidad, pero no se deja.
¿Alguna sugerencia?
Un saludo.
Y gracias.

Jorge L. Dunkelman 05 febrero, 2013 06:48  

Hola,
todo lo que hay que hacer es quitar "as String" en la declaración de la función

Function find_last_value(Valor_Buscado As Variant, _
Vector_de_Comparacion As Range, _
Vector_de_Resultado As Range) As String

de manera que quede

Function find_last_value(Valor_Buscado As Variant, _
Vector_de_Comparacion As Range, _
Vector_de_Resultado As Range)

Alex González 04 junio, 2013 15:56  

Hola Jorge,

En tu penúltima comentario diste una fórmula con la que se obtenía el último valor númerico de un rango sin importar si habían celdas vacías o con texto.

La fórmula que indicaste era la siguiente:

=BUSCAR(2,1/ESNUMERO(C3:C30),C3:C30

He intentado analizar la fórmula que en principio parece simple pero aunque cumple su cometido perfectamente no le encuentro la lógica. Se supone que con ella buscamos el valor 2 en un rango comprendido entre las celdas C3 y C30. Ignoro porqué como valor buscado se indica el número 2 y como vector de comparación 1/ESNUMERO(C3:C30). El vector de resultado parece claro que deber ser el mismo pero sobre los dos argumentos anteriores sigo sin encontrarles la lógica. Sería mucha molestia que explicases cómo funciona dicha fórmula?

Saludos y gracias por adelantado.

Álex.

Jorge Dunkelman 05 junio, 2013 07:29  

Hola Alex,

en la parte fina de esta nota hay una explicación.
BUSCAR, a pesar de lo que dice la ayuda en línea de Excel, da como resultado el última valor del vector cuando no encuentra coincidencia. Ahora, el argumento ESNUMERO(C3:C30) da una serie de valores VERDADERO o FALSO; en la expresión 1/ESNUMERO(C3:C30) VERDADERO toma el valor 1 y FALSO el valor 0. Resulta así un vector de 1 y #DIV0. BUSCAR encuentra el último valor menor o igual al buscado, es decir el último 1. Podrías usar también 3 o cualquier número igual o mayor a 1 como primer argumento de la función.
También podés usar el auditor de fórmulas para ver como funciona paso a paso.

Miriam Vidal 05 julio, 2013 01:21  

Hola jorge buena tarde, tengo un problema similar al que te estan enviando el detalle es que yo tengo una tabla con 97 columnas en ellas vienen precios y algunas estan en cero porque no hubo entrada, lo que necesito es que me tome la formula el ultimo precio de entrada, pudiendo estar en la columna C, o en la columna X y que no tome los ceros si no que me de el ultimo precio de entrada y no me tome en cuenta los ceros, intente la formula que usas, pero no me da yo la tendria que aplicar asi :

=BUSCAR(97,1/ESNUMERO(C5:T5),C5:T5)
o bien tengo que anotar cada uno de los numeros es decir :

=BUSCAR(97,96,95,94...,1/ESNUMERO(C5:T5),C5:T5)
me ayudarias por favor?

Gracias de antemano

Jorge Dunkelman 06 julio, 2013 11:06  

Miriam

fijate en la técnica que muestro en esta nota.

omar6103 12 septiembre, 2013 16:39  

Ahora, ¿cómo le hago para borrar el último valor de la columna encontrado con la función buscar?

Jorge Dunkelman 12 septiembre, 2013 22:52  

No se puede borrar el contenido de una celda con fórmulas. Tienes que seleccionar la celda y borrar el contenido.

Rubén Alba 26 enero, 2014 03:08  

Hola Jorge,
He leído aquí que le explicabas a un usuario que sí lo que quiere es buscar texto, debía poner "zzzzz". Estoy haciendo pruebas para ver si lo consigo y poniendo nombres al azar sólo me da el nombre que más próximo está a la Z. Por ejemplo: Juan, Manolo, Pepe, Lucas, aunque Lucas sea el último, me da Pepe.
Cómo podría solucionarlo? Gracias

Jorge Dunkelman 26 enero, 2014 06:38  

Hola Rubén, =BUSCAR("zzzz",A1:A4), donde el rango A1:A4 contiene los nombres de tu ejemplo, da Lucas. Por lo visto hay algo errponeo en los argumentos de tu fórmula.

Leonardo Alaniz 24 abril, 2014 19:48  

Hola Jorge: quería consultarte sobre un tema que no logro resolver adecuadamente: Tengo este caso
A B C
Cod: Fecha Acción
6004665 10/03/2014 E-mail
6004665 11/03/2014 LLamado
6004665 12/03/2014 Visita
18353 10/03/2014 E-mail
18353 13/03/2014 Llamado

Necesito poder registrar la ultima acción sobre cada código (que se repite varias veces) de cliente que quede de la manera que estoy mostrando. Desde ya agradezco cualquier colaboración al respecto.
Slds

A B C
Cod: Última Fecha Última Acción
6004665 12/03/2014 Visita
18353 13/03/2014 Llamado

Jorge Dunkelman 24 abril, 2014 21:49  

Hay varias posibilidades, dependiendo de cómo estén organizados los datos y de la cantidad de registros (filas). Una posibilidad es crear una columna auxiliar para encontrar la fila que cumple con las condición de ser la última fecha de cada cliente y filtrar la lista de acuerdo a esta columna.
En tu ejemplo agregamos una columna entre Fecha y Acción (será la columna C) que llamamos Aux. En esta columna C ponemos esta fórmula matricial

=MAX(($A$2:$A$6=A2)*$B$2:$B$6)=B2

apretando Ctrl-Mayúsculas-Enter simultáneamente. Esto da como resultaddo FALSO o VERDADERO (si se cumplen ambas condiciones). Luego al filtrar la lista por Aux, serás las últimas acciones de cada cliente.
Te sugiero que veas lo que pongo en el enlace Ayuda, en la parte superior de la plantilla.

Salomon Amiga 16 febrero, 2015 00:33  

Hola, tengo este problema, que no encuentro la solución
Tengo esta base de datos

https://drive.google.com/file/d/0B102ymFDxRHpbGNCWm5RQi0yQ2M/view?usp=sharing

Es una lista de asistencia, en la cual pongo cada semana quien asiste con un *

Necesito que en la columna C me indique cada persona cuando fue la ultima vez que asistió, use al formula de buscar pero no me sirve por que no están ordenados de forma ascendente, pero no tengo manera de ordenar mi tabla de esa forma

Alguien se le ocurre alguna otra manera de realizar esta función ?

De antemano, muchas gracias !

Jorge Dunkelman 16 febrero, 2015 19:36  

Salomon,
es una mala idea usar * para señalar valores ya que el símbolo * es un wildcard que quiere decir "todo valor".
Te sugiero que leas las instrucciones en el enlace Ayuda (en la parte superior de blog) y te pongas en contacto conmigo por mail privado.

Anónimo,  16 junio, 2015 15:21  

Hola! Necesito ayuda. Estoy buscando la forma de hacer que me busque el primer valor de una fila y me devuelva el encabezado de esa columna. Es decir, tengo 12 meses (en fila) y nombres de clientes (en columna). Si en un mes no ha comprado esa celda no tiene valor, pero si ha comprado aparece el valor. Lo que necesito saber en qué mes hizo la primera compra. Sólo he conseguido la fecha de última compra con BUSCAR.
Muchas gracias!

Unknown 01 diciembre, 2015 19:05  

Jorge, tengo una consulta, tengo una matriz en la que necesito buscar el resultado mas reciente de fechas, con valores repetidos.
XXXX8892910 P3 04/12/2014
ZCSU2366473 P2 04/08/2015
XXXX8892910 P3 01/11/2015
ZCSU2366473 P2 01/12/2015
XXXX8892910 P3 28/11/2015


en la primera columna se ven valores repetidos, necesito que el buscarv me de la fecha mas reciente.

Te dejo mi Mail

Gibzakush@gmail.com
Gonzalo Martinez Fuentes

Muchas gracias

Jorge Dunkelman 02 diciembre, 2015 07:08  

Lo más sencillo sería ordenar la lista por fechas. De esa manera BUSCARV daría como resultado la fecha el valor buscado con la fecha más reciente.

ferpebe 15 diciembre, 2016 13:31  

Hola, Jorge, tengo una de facturacion con este formato:
1/12/16 15,25
1/12/16 10,35 25,60
2/12/16 7,25
2/12/16 5,10
2/12/16 6,30 18,65
Como ves, en la A va la fecha de factura, en la B el importe y en la C totaliza las ventas de cada día.
Lo que quiero ahora es en otra hoja hacer un listado del total de ventas diaria, es decir, en A cada dia del año, y en B que aparezca el total de ese dia, como podría hacerlo?
Gracias campeon

ferpebe 15 diciembre, 2016 15:28  

Nada, ya esta, gracias de todas formas, lo he solucionado con
=SUMAR.SI(Facturas!$B$2:$B$10000;TOTALES!A2;Facturas!$F$2:$F$10000)

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP