Excel BUSCARV (VLOOKUP) a través de varias hojas

sábado, mayo 12, 2007

Supongamos que queremos usar BUSCARV, pero la matriz de búsqueda no se encuentra en una sola hoja como sucede por lo general, sino en varias hojas de un mismo libro.
En este ejemplo tenemos una lista de ciudades de la Argentina y su población de acuerdo a los censos de 1992 y 2001. La lista se encuentra repartida entre las hojas Hoja2, Hoja3 y Hoja4.
En la celda A1 escribimos el nombre de una las ciudades y en la celda A2 queremos poner una fórmula que nos de la población de la ciudad. La función indicada para esta tarea es BUSCARV, sólo que tenemos que indicarle en qué hoja realizar la búsqueda.
Como en muchos otros casos tenemos dos opciones: usar funciones "nativas" de Excel o escribir una función UDF (función definida por el usuario).

Si queremos limitarnos al uso de funciones nativas de Excel, una solución posible es

=SI(ESNOD(BUSCARV(A1,Hoja2!A:D,4,0)),SI(ESNOD(BUSCARV(A1,Hoja3!A:D,4,0)),BUSCARV(A1,Hoja4!A:D,4,0),BUSCARV(A1,Hoja3!A:D,4,0)),BUSCARV(A1,Hoja2!A:D,4,0))

Aquí usamos una cadena de condiciones de manera que si la búsqueda en la Hoja2 da un resultado erróneo (que evaluamos con la función ESNOD), pasamos a una segunda condición que evalúa entre la Hoja3 y la Hoja4.

Esta solución tiene varios inconvenientes:
- si agregamos otra hoja al libro, tendremos que modificar manualmente la fórmula
- la función SI esta limitada a 7 condiciones

El sitio Ozgrid.com propone esta función definida por el usuario que da respuesta a estos problemas.

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _
Col_num As Integer, Optional Range_look As Boolean)

''''''''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid.com
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup _
(Look_Value, Tble_Array, _
Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet

Set Tble_Array = Nothing
VLOOKAllSheets = vFound
End Function

Para usar esta función hay que copiarla a un módulo del editor de VB. Luego podemos usar el asistente de función en la categoría "definidas por el usuario".

Esta función usa la función BUSCARV nativa de Excel (WorksheetFunction.VLookup) aplicándola cada vez a otra hoja (For Each wSheet In ActiveWorkbook.Worksheets).
Cuando la función da una resultado no vacío, la rutina se interrumpe y el valor hallado es pasado a la función.
Para que WorksheetFunction.VLookup de resultados "vacíos" en lugar de error, usa On Error Resume Next.

Esta función definida por el usuario usa los mismos argumentos como la función BUSCARV nativa de Excel





Technorati Tags:

80 comments:

Anónimo,  28 mayo, 2007 20:12  

Hola, te agradezco mucho la macro de convertir numeros a letras, le encontre unos errores que modifique y me gustaria que tubieras.
mandame tu correo te la mando, mi correo es Blackdays13@hotmail.com

Jorge L. Dunkelman 28 mayo, 2007 22:20  

Hola, mi correo es dunk@maaganm.co.il

Anónimo,  31 agosto, 2007 17:28  

si podrian ayudarme se los agradeceria tengo varias listas con ranking de venta de productos pero la funsion buscar o buscarv
cuando le pongo la matris o vector de comparacion nunca buscan correctamente son codigos y nombres de productos
si puede ayudarme muchas gracias

Jorge L. Dunkelman 31 agosto, 2007 22:41  

Hola

la única forma en que puedo ayudarte es si me mandas el archivo con el problema (jorgedun@gmail.com).

Antonio,  13 diciembre, 2007 04:27  

Buenas Noches!!! llevo bastante tiempo visitando su Blog y en verdad me ha sido mas que funcional para mis labores, en especial esta forma de buscar en todo el libro, mas sin embargo como sabemos en ocasiones el dato deseado se encuentra a la izquierda del valor encontrado por lo que, como usted mostro, usamos indice y coincidir, podriamos hacer lo mismo que el buscarv en todas las hojas para las formulas de indice y coincidir??

Agradezco mucho su apoyo!!
Saludos

Jorge L. Dunkelman 13 diciembre, 2007 20:49  

Hola Antonio

lo que tienes que hacer es reemplazar la función BUSCARV en la fórmula =SI(ESNOD(BUSCARV(... por la combinación INDICE y COINDICIR
Lo mismo en el código de la macro.

Antonio,  15 diciembre, 2007 03:16  

Es de mucha ayuda tu apoyo pero seria mucha molestia si tienes el codigo de VB?? ya que no tengo el nombre de las funciones en ingles para las macros.. por fass

Jorge L. Dunkelman 16 diciembre, 2007 18:17  

Hola Antonio

no entiendo a qué te refieres, ya que el código está publicado en el cuerpo de la nota y VB usa los nombres de las funciones en inglés.
De todas maneras, la única función en el código es VLOOKUP en castellano BUSCARV

Anónimo,  20 febrero, 2008 22:18  

Hola, muy ilustrativo el codigo de la macro, he intentado modificarlo para que me busque un valor en otro libro indicando el nombre de archivo pero no resulta, como puedo hacerlo

Jorge L. Dunkelman 20 febrero, 2008 22:27  

El libro remoto está abierto? Puedes fijarte en esta nota

Hector Benitez,  09 mayo, 2008 10:34  

Jorge, tu tips me han sacado de varios apuros, ademas de darme mas herramientas para hacer mejor mi trabajo,tengo esta 'issue' que no he podido resolver, espero que puedas ayudarme: tengo el archivo 'A' donde necesito informacion del archivo 'B' & 'C': quiero hacer un VLOOKUP donde me traiga el dato buscando en los 2 archivo 'B' & 'C'.

Jorge L. Dunkelman 09 mayo, 2008 13:33  

Hola Hector
la respuesta a tu consulta excede el marco de un comentario. Estaré publicando una nota sobre el tema, espero que en un futuro cercano.

luzenith 17 julio, 2008 13:19  

Hola, soy Shoke, mi pregunta es si existe este mismo script pero para "buscarh" en vez de "buscarv"

Jorge L. Dunkelman 17 julio, 2008 18:56  

Hola

reemplazando BUSCARV por BUSCARH (en la mcro HLOOKUP)

luzenith 18 julio, 2008 15:21  

Gracias por la respuesta lo tengo funcionando, pero tengo un problema, si hago un cambio en la celda fuente de la que procede el dato buscado (en mi caso es para que saque una tarifa desde un escandallo de articulos) que es muy cambiante, no se actualiza el nuevo valor automaticamente, tengo que entrar editar la formula y al salir de la formula se actualiza. ¿No hay una manera de que lo haga automaticamente, que sea tipo pulse F5 para ejecutar script o algo asi?
Gracias de antemano.

luzenith 18 julio, 2008 17:25  

vale, ya no hace falta, actualizar con Ctrl+Alt+F9.

Jorge L. Dunkelman 19 julio, 2008 09:35  

Tanto la solución con funciones nativas como la función definida por el usuario tienen que actualizarse automáticamente, e menos que la opción Cálculo este definida como manual.
Fijate en el menú Herramientas--Opciones, en la pestaña cálculo, que esté marcada la opción Automático.

Anónimo,  22 agosto, 2008 17:10  

me fue de gran ayuda :)

Pegui,  02 septiembre, 2008 18:20  

Jorge.

En relacion al VlookUp es posble poner la table en un libro que serà utlizada por otros libros ?

Ejemplo: Tengo 12 libros, uno para cada mes de 2008. En cada libro quiero usar la funcion Vlookup que siempre buscarà en una misma tabla. En lugar que esa tabla este en cada libro, puede estar en un libro aparte ? digamos en el Libro Nro13. Como se hace escribir la formula en cada libro que utilizarà la tabla que pondrè en el Nro13 ? Es posible ?

Gracias, por tu tiempo.

Jorge L. Dunkelman 02 septiembre, 2008 21:59  

Si, se puede usar BUSCARV con referencias remotas (a otro libro). Cuando abras los cuadernos de los años, Excel te preguntará si querés actualizar los datos.

Anónimo,  20 octubre, 2008 03:58  

hola jorguito... te cuento que ya estoy comprendiendo todo esto, pero y casi me da resultado la formula del SI con buscarv, pero yo kiero hacerlo en forma de listado mi busqueda, casi me da resultado, el problema es q los datos de la Hoja3 sale "Falso" y no entiendo que estoy haciendo mal...
gracias

Jorge L. Dunkelman 20 octubre, 2008 18:36  

Lo que tenés que hacer es mandarme el archivo con lo que querés hacer, ya que de la descripción es un poco difícil entender lo que estás haciendo. Del resultado FALSO que recibís deduzco que en la función SI te falta el resultado para el caso de no cumplirse la condición.
Ah!, escribir en castellano también ayuda ("kiero"?)

Anónimo,  10 noviembre, 2008 14:46  

Lo primero de todo es descubrirme ante todo lo que sabes y el favor que nos haces publicándolo.
A ver si consigo explicarlo sin liarme mucho.
Tengo libro Excel en el que tengo varias hojas con valores abreviados y su correspondencia. Lo que pretendo es que en la hoja1 tendremos una búsqueda y dependiendo del valor vaya a buscarlo a la hoja que le corresponda (este valor lo puede coger de la fila 1 de su misma columna)
Estoy intentando modificar esta función para que en vez de utilizar el For para recorrer todas las hojas me coja una cuyo nombre estará en la fila 1 de esa hoja (o la celda que le asigne), pero no soy capaz de asignar el valor de la celda a la variable wsheet.
En concreto quiero que crear una función BuscaV donde el nombre de la hoja a buscar también me lo pida, pero que sea el valor de una celda.
No se si me habré explicado con claridad, mucho me temo que no. ... :o(
Ante todo muchas gracias por tu tiempo.

Jorge L. Dunkelman 13 noviembre, 2008 21:53  

Tienes que crear una referencia a la hoja usando la función INDIRECTO.
La mejor manera sería dar un nombre (Insertar-Nombres-Definir) a cada rango de cada hoja y luego usar este nombre como refrencia. Por ejemplo, has definido los nombres rango1, rango2, etc. En la celda A1 pones el nombre del rango y en la celda donde quieres obtener el resultado de la búsqueda pones algo así

=BUSCARV(valor a buscar;INDIRECTO(A1);indicador_columnas;0)

Anónimo,  03 diciembre, 2008 21:19  

Hola Jorge, gracias de antemano por toda la ayuda.
Quiero usar VLOOKAllSheets buscando los datos en otro libro, no en hojas del mismo libro y no consigo que funcione con INDIRECTO.¿es posible añadir el libro origen de los datos en el código de la función?
Gracias de nuevo,
Borja

Jorge L. Dunkelman 04 diciembre, 2008 23:12  

En principio si. Pero la función INDIRECTO no funciona si los libros de referencia están cerrados. Puedes fijarte en esta nota.

Anónimo,  18 febrero, 2010 22:04  

Hola, tu blog me ha sido muy util muchas veces pero esta vez tengo una consulta que hacer. Si tengo 2 tablas en las que entre otros datos, tengo horas q no coinciden, deseo juntarlas relacionando esas horas considerando las mas cercanas entre ellas, es decir, si una es 10:02:03 y la otra es 10:03:56 (la mas cercana) que las relacione. Respondeme a lobox09@hotmail.com por favor. Muchas gracias

Jorge L. Dunkelman 19 febrero, 2010 18:31  

No se a que te referías con juntar, pero podrías agrupar los datos por minutos o por horas usando "Agrupar" en una tabla dinámica. Si el intervalo para agrupar los datos no es constante, sería más problemático.
Te sugiero que mem mandes tu consulta con un ejemplo. Fijate en las instrucciones en la pestaña Ayuda (en la barra superior del blog).

Anónimo,  12 junio, 2010 17:40  

muy buen aporte gracias justo lo que buscaba

Anónimo,  23 julio, 2010 21:20  

Que tal amigos, muy buena solucion, pero tengo una duda, como le hago para que no busque en la hoja donde estoy poniendo la fórmula??? ... Salu2!

Jorge L. Dunkelman 24 julio, 2010 10:33  

Habría que modificar el ódigo poniendo una condición para que no evalúe la hoja que contiene la fórmula. Por ejemplo, agregamos una variable que contenga el nombre de la hoja con la fórmula (llamémosla "formSheet"), ya agregamos

formSheet = ActiveSheet.Name

para definir la variable y luego modificamos el código así

For Each wSheet In ActiveWorkbook.Worksheets
If wSheet.Name <> formSheet Then
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup _
(Look_Value, Tble_Array, _
Col_num, Range_look)
End With
End If
If Not IsEmpty(vFound) Then Exit For
Next wSheet

Anónimo,  28 octubre, 2010 22:02  

Que tal amigos, Aplicando la formula si(esnod)me funciona pero incompleta es decir tengo un producto en una de las 5 hojas pero me devuelve el valor FALSO y no el que quiero

Jorge L. Dunkelman 29 octubre, 2010 07:24  

Pareciera ser que el valor de búsqueda no existe en ninguna de las cinco hojas.

Anónimo,  27 diciembre, 2010 20:17  

Hola JLD,

Me ha venido estupenda tu ayuda; estoy tratando de hacer las modificaciones necesarias para adaptar el buscarV al buscarH. Sin emabargo, me he dado cuenta que en el fichero que has puesto a modo de ejemplo, si cambio "Buenos Aires" por cualquier ciudad que está en la hoja3, el resultado es FALSO; y no trae los datos de esta hoja. Alguna forma de solucionarlo?.

Muchas gracias.

Oscar WV.
dreamwitt@hotmail.com

Jorge L. Dunkelman 01 enero, 2011 10:33  

Tienes razón, después de tanto tiempo sos el primero que vio el error en la fórmula. He actualizado la nota y el enlace.

Jorge L. Dunkelman 01 enero, 2011 10:41  

Releyendo los comentarios veo que ya hace más de dos años un lector me había señalado el problema. Bien, más vale tarde que nunca....

nericito 26 junio, 2011 08:44  

les agradezco mucho esta publicación, la he usado mucho en mi trabajo, sin embargo tengo un inconveniente el cual es que en mi office de mi comp de mi casa no me aparece el BUSCARV, del todo, como hago para cargar esta fórmula en mi office???

Jorge L. Dunkelman 26 junio, 2011 10:09  

Si tienes Excel (no importa que versión), tienes BUSCARV. En Excel 2010 la función fue rebautizada como CONSULTAV.

Anónimo,  19 agosto, 2011 15:41  

Hola
estoy haciendo un bucarv bastante sencillo pero no me funciona, tengo una tabla con registros de A1:DO51937 y necesito llamar datos de esta a otra tabla, las dos tablas tienen un dato que me coinciden, pero sigo los pasos y me muestra un dato que no corresponde y cuando lo hago en las siguientes filas para ver que pasa me sigue mostrando ese dato igual.

Te agradezco tu ayuda.

Jorge L. Dunkelman 19 agosto, 2011 18:32  

Posiblemente la opción de cálculo está puesta a "manual". Fijate en "Opciones de Excel-Fórmulas". Si necesitas por algún motivo (tal vez el tamaño del cuaderno) dejar el método de cálculo en "Manual" tienes que apretar F9 par forzar el recálculo de la hoja.

SirJB7 27 octubre, 2011 22:31  

Jorge, hola! Tengo el siguiente problema con todas las funciones definidas por el usuario (UFD). Actualizo una celda que es argumento de dicha función en otra celda, y ésta última no se actualiza. El módo de cálculo está en automático, solo me funciona si edito la celda que contiene la función y le doy enter (la modifico dejándola igual). Tampoco funciona F9, pero hoy leyendo tu post, noté que con Ctrl-Alt-F9 sí funciona. Hay alguna forma de que las celdas con UFD se actualicen automáticamente, sin necesidad de editarlas o de C-A-F9? Gracias. Pablo.

Jorge L. Dunkelman 28 octubre, 2011 07:51  

Pablo,
En general las UDF son recalculadas de la misma manera que las funciones nativas de Excel.
Cuando una UDF es afectada por rangos/celdas que no figuran en la lista de argumentos, la UDF puede no ser recalculada.
Para remediar esta situación la solución recomendada es poner todas las referencias en la lista de argumentos de la función.
Otra solución es convertir la UDF en volátil agregando

Application.Volatile(True)

al comienzo del código.

lord_anime 30 noviembre, 2011 19:17  

Muchas gracias

me fue muy util esta funcion

Anónimo,  09 diciembre, 2011 21:56  

Gracias Jorge en este blog resolví prácticamente mi problema.
D.. ¿Impartes algun curso personalizado?
me interesaria civalsi@hotmail.com

Iván Sosa T

Jorge L. Dunkelman 10 diciembre, 2011 07:21  

Me alegro que te haya resultado útil. No, no imparto cursos.

José Luis 04 enero, 2012 00:45  

Jorge, No logro recalcular las celdas.

Ayuda por favor.

Saludos

Jorge L. Dunkelman 04 enero, 2012 07:36  

José Luis,
fijate en los comentarios del 19 e agosto y del 28 de octubre.

Anónimo,  25 febrero, 2012 02:50  

hola, gracias por tu ayuda, me sirvio de muco

Anónimo,  05 abril, 2012 15:24  

Hola Jorge, ya no es sólo que tu capacidad y conocimmientos me parezca inauditos, que por lo general observo que le ocurre a casi todos, lo peor es que me da la sensación de que todos los participantes del foro están tan puestos en fórmulas de Excel, que yo que me estoy iniciando, casi no puedo usar nada de lo que habláis, porque dais por hecho muchas cosas básicas que yo desconozco.
Lo mio debe ser my básico, así que supongo que para vosotros la fórmula será sencillísima de realizar, pero yo ya he realizado mil pruebas y todas me dan error.
El caso es que tengo un listado en La hoja 1, en el que en la columna C aparecen cientos de códigos, cada uno en su casilla, y organizados verticalmente, y cada código tiene un nombre asignado en la celda contigua de la columna D.
Por otro lado, dispongo de un nuevo listado idéntico al anterior, en la hoja 2, pero con las casillas de la columna D vacías, y que quiero cruzarlo con el primero, para que cuando algún código del segundo listado, ya exista en el primero, automáticamente me copie a la casilla de la columna D el nombre asignado a ese código en la casilla D contigua del primer listado.
De todo lo que he probado creo que lo que más se ha aproximado es lo que sigue:
He puesto en la casilla D del segundo listado lo siguiente:
=SI(ESNOD(BUSCARV(C,Hoja2))=SI(ESNOD(BUSCARV(C,Hoja1))D,Hoja2=D,Hoja1 pero no me sale!
Me puedes ayudar?
Leo.

Jorge L. Dunkelman 09 abril, 2012 19:30  

Leo,
como pingo en el enlace Ayuda, tendrías que enviarme el archivo. También te sugiero que publiques tu consulta en alguno de los muchos y buenos foros sobre Excel que hay en la Internet.

Anónimo,  10 abril, 2012 10:49  

Disculpa Jorge, ya lo he visto todo bien detalladito en el enlace Ayuda. Siento haber sido demasiado impulsivo, escribiendo en el foro antes de ver el enlace.

Por cierto, no se si será porque me estoy haciendo mayor... o si se deberá a que normalmente no suelo estar enviando sms´s continuamente, pero me encanta el recelo con el que proteges nuestra lengua común.
Un abrazo.
Leo.

Carlos Berzunza 19 junio, 2012 21:01  

Hola, miren, tengo un problema. Debo sacar un concentrado de nominas de varios años, por lo que he hecho un libro por año, en cada libro tengo por hojas las nominas quincenales, primas vacacionales, aguinaldo, y retroactivos. Generé una hoja principal en donde deseo que me localice por hoja a la persona de la celda a7 y me sume los valores por columna de sueldo/prev social/etc y e devuelva las sumas correspondientes a ese nombre independiente de la posición que tenga ese nombre en la nomina que sea. son 28 hojas. espero me puedan ayudar, mi mail es crberzun@gmail.com tengo el office 2010 y esta pagina me ha sacado de mil problemas.

Carlos Berzunza 20 junio, 2012 20:37  

Jorge: buen día he buscado por diversos sitios en internet y pues eres la única opción felicidades por eso. Mira en mi comentario anterior coloque unas dudas. Mira ya copie la macro en el excel 2010, no me hace la suma de todas las hojas del libro. ¿qué estará fallando? necesito un concentrado que sume por concepto (sueldo, prev social, etc) de todas las quincenas del año. Por cada trabajador, de forma que en una sola hoja se presenten todos los datos sumados por cada uno de los trabajadores. En serio que creo que ando ciclado pero no lo consigo hacer. Espero puedas concretar una respuesta ya que en serio que voy por el segundo día. Muchos Saludos.

Jorge L. Dunkelman 20 junio, 2012 20:50  

Carlos, es una muy, pero muy, mala idea poner tu dirección de correo electrónico en un comentario. Estás poniendo en peligro tu correo!
En cuanto a tu consulta, no hay ningún motivo para dividir los datos en distintos cuadernos (uno por añlo y si entiendo tu comentario cada cuaderno contiene varias hojas). Al dividir los datos de esa manera nos aseguramos el camino más corto al infierno (de Excel).
Te sugiero que reorganices tus datos en una única hoja (en Excel 2007/10 tienes a tus disposición más de un millón de filas), agregando un campo pra el año. Una vez organizados los datos de esa manera, una tabla dinámica te resumirá los datos de cualquier manera que requieras en fracciones de segundos.

ERIKA,  05 julio, 2012 06:33  

Buenas Noches Jorge, me puedes ayudar quiero que en una hoja me traiga todos los datos que encuentre en todas las hojas del libro, tengo varias hojas que cada una es un mes del año, cada hoja tiene el listado de apartamentos y el consumo del mes, necesito es que en una nueva hoja me muestre los datos el consumo del mes por cada mes según el apartamento que digite.

Muchas Gracias

Jorge L. Dunkelman 06 julio, 2012 17:18  

Al igual que en el comentario anterior, te sugiero que reorganices tus datos de manera de crear una única tabla/base de datos. ¿Por qué dividir los datos en hojas por mes?
Una vez organizados así, con una tabla dinámica resuelves el problema en forma eficiente.

Anónimo,  23 octubre, 2012 23:08  

Hola jorge
tengo el siguiente problema, cuento con un libro de muchas hojas (creando periodicamente nuevas)cada una guarda datos individuales (ordenes de compra)
y necesito generar un listado resumen, donde ingrese el numero de hoja y esto me indique ciertos datos como nombre del proveedor, fecha, monto,etc.

espero haberme explicado bien
tu respuesta será de gran ayuda.

Jorge L. Dunkelman 25 octubre, 2012 11:23  

La mejor solución sería guardar los datos de las órdenes de compras en una única hoja/tabla. Eso te permitiría extraer datos y hacer análisis y resúmenes con facilidad. ¿Por qué motivo guardas los datos de cada orden en una hoja? ¿Que pasará cuando tengas miles de órdenes?
Pero siguiendo con tu consulta, tendrías que programar una macro que genere el resúmen. En esta nota muestro una técnica para hacerlo.

Alejandro,  14 diciembre, 2012 01:55  

Hola Jorge
queria hacerte una consulta porque una formula no funciona bien. Tengo 3 hojas, en la 1 tiene: codigo, descripcion, costo y precio; en la 2 tiene: codigo, descripcion, cantidad y costo; y la 3 la armo de la siguiente manera: codigo, descripcion, cantidad, costo (hoja1), precio y costo (hoja2). Aclaro que en la hoja 1 hay codigos que no estan en la 2 y viseversa, pero la mayoria de los codigos coinciden. En la hoja 3 copio los codigos de la hoja 1 y a continuacion los de la hoja 2, luego elimino los duplicados y ordeno los codigos de menor a mayor (en hojas 1 y 2 estan ordenados). A continuacion utilizo las siguientes formulas: descripcion: =SI.ERROR(BUSCAR(A4;Hoja1!$A$4:$A$10002;Hoja1!$B$4:$B$10002);BUSCAR(A4;Hoja2!$A$2:$A$10000;Hoja2!$B$2:$B$10000)), cantidad =SI.ERROR(BUSCAR(A4;Hoja2!$A$2:$A$10000;Hoja2!$C$2:$C$10000);0), costo (hoja1) =SI.ERROR(BUSCAR(A4;Hoja1!$A$4:$A$10002;Hoja1!$C$4:$C$10002);0), precio =SI.ERROR(BUSCAR(A4;Hoja1!$A$4:$A$10002;Hoja1!$D$4:$D$10002);0) y costo (hoja2) =SI.ERROR(BUSCAR(A4;Hoja2!$A$2:$A$10000;Hoja2!$D$2:$D$10000);0). Ahora pongo un ejemplo: en la Hoja1 tengo los codigos 3, 4, 5, 6, 8; en la Hoja2 los codigos 1, 2, 3, 4, 7, 8; por lo tanto en la Hoja3 quedan los siguientes codigos: 1, 2, 3, 4, 5, 6 7 y 8. Como resultado obtengo: codigos 1, 2, 3 y 4 los resultados son correctos, pero en los codigos 5 y 6 en donde tendria que poner 0 en cantidad y costo (hoja2), me repite los valores del codigo 4; y en el codigo 7 que tendria que poner 0 en costo (hoja1) y precio, repite los valores del codigo 6. Espero haber sido mas o menos claro y que me ayudes a solucionar el problema. Desde ya muchas gracias.

Jorge L. Dunkelman 14 diciembre, 2012 10:59  

Alejandro,
por favor, fijate lo que pongo en el enlace Ayuda (en la parte superior de la plantilla) en lo que hace a ese tipo de consultas.

Alejandro,  14 diciembre, 2012 23:59  

Hola Jorge,
disculpa que no haya leido la Ayuda, ahora te envio el archivo, muchas gracias.

Alejandro,  17 diciembre, 2012 16:31  

Hola Jorge,
muchas gracias por tu ayuda, ya solucione el problema.

Eusebio Obdulio Mirondo 07 enero, 2013 16:37  

Que tal Jorge?... mi pregunta va referida a lo siguiente. Asi como existe la posibilidad de buscar un dato dentro de una Hoja!, necesito saber como hacer la inversa. Buscar en que Hoja! se encuentra determinado dato. Es decir, yo tengo el dato pero tengo que buscar en que Hoja! esta ya tengo muchas Hojas!, y no se en cual esta.
Existe alguna formula para dicha tarea?.

Jorge L. Dunkelman 07 enero, 2013 20:48  

La forma de hacerlo en con Vba (macro), pero siempre se puede hacer una búsqueda en la Internet; siempre aparece algún "monstruo" que le encuentra solución a todo con fórmulas.

Elvis Sanchez 27 enero, 2013 21:50  

hola yo estoy buscando una formula que busque el nombre de la hoja (que es una fecha de año) y de allí tomar los datos que necesito.

Jorge L. Dunkelman 28 enero, 2013 07:23  

Se puede extraer el nombre de la hoja activa con esta fórmula

=DERECHA(CELDA("nombrearchivo"),LARGO(CELDA("nombrearchivo"))-HALLAR("]",CELDA("nombrearchivo"),1))

Pero, si ya estamos en la hoja activa, ¿para qué necesitamos extraer el nombre?

juan fernández 12 febrero, 2013 12:28  

Hola Jorge. Mi comentario viene a propósito de la pregunta que te hacía Elvis Sánchez unas líneas más arriba.
Me preguntaba si habría alguna forma de hacer eso mismo, armar una formula que diera el nombre de la hoja con una función, pero de manera que se "fijara", esto es, que el resultado de la función no variara al cambiar de hoja activa.
Gracias

Jorge L. Dunkelman 13 febrero, 2013 07:37  

Las fórmulas se recalculan cada vez que hay un cambio en la hoja. Para que un valor permanezca sin cambios en la hoja tiene que ser una constante.

MAURICIO PERDOMO LAGUNA 13 marzo, 2013 20:00  

Jorge, buen día.
Por favor, requiero de sacar en celdas los días de la quincena del 1 al 15 y/o del 16 al 30 o 31 y su correspondiente día de la semana, Lu, Ma, Mi, Ju, Vi, Sa, Do; respecto a la selección de la primera o segunda quincena del respectivo mes seleccionado en celdas desplegables, ya tengo los rangos de los días del año y lo días de la semana pero no encuentro la formula para expresar la función.
Gracias

Jorge L. Dunkelman 17 marzo, 2013 20:30  

Mauricio, no termino de entender la consulta. ¿Expresar qué función?

Katta,  26 marzo, 2013 16:54  

Hola Jorge
Pues bastante interesante la nueva función desafortunadamente no me sirve para lo que estoy tratando de hacer actualmente, pero sin duda la tengo muy presente pues creo que me servirá para otros proyectos en los que estoy trabajando.
Muchas gracias por compartir tus conocimientos y hacer la vida un pelin mas simple al resto de los mortales =)

Anónimo,  20 agosto, 2013 18:42  

Buen dia Jorge: Muy buena la función de búsqueda y me ha sido muy útil para solucionar parte de mi problema. El tema es que estoy necesitando que esta función no se interrumpa cada vez que encuentra el valor buscado sino que siga buscando en las demás hojas ya que cada vez q encuentra el valor buscado debe suma el contenido de la columna indicada a un acumulado. Espero puedas ayudarme con esto.
Gracias

Jorge Dunkelman 20 agosto, 2013 20:14  

Bien, esta función no está diseñada para solucionar tu problema. Se puede hacer con funciones y también con macros. Seríe bueno que describas un poco más como están organizados los datos. Por ejemplo, puedes mandarme un cuaderno con el ejemplo (fijate en el enlace Ayuda, en la parte superior de la plantilla). Parece un tema interesante y podría publicar una nota con la solución.

fernando 27 octubre, 2013 17:22  

Hola Jorge:
Utilizo BUSCARV para obtener datos que tengo en distintas hojas dentro de un libro. Todas las hojas tienen la misma estructura y los datos que necesito están en el rango C67:K74 de cada una de ellas. He planteado la siguiente fórmula “=BUSCARH(A1;’NOMBRE_HOJA’!$C$67:$K$74;7;FALSO)”.
Funciona bien, pero he de cambiar manualmente “NOMBRE_HOJA” cada vez que la utilizo. El nombre de cada hoja puedo ponerlo en celdas de la hoja activa, pero no consigo que la fórmula haga referencia a ellas y así automatizar el proceso. ¿Se te ocurre alguna solución?. Gracias.

Jorge Dunkelman 27 octubre, 2013 21:26  

Hola,
con la función INDIRECTO. Creás un cadena de texto que representa la referencia y la ponés como argumetno en la función INDIRECTO. Algo así como

=BUSCARH(A1;INDIRECTO(A1&"!$C$67:$K$74");7;FALSO)

donde A1 es la celda que contiene el nombre de la hoja.

fernando 28 octubre, 2013 15:58  

Gracias Jorge, la función INDIRECTO va perfectamente para hojas de un mismo libro,
pero tengo otras hojas en otros libros (archivos) de similar estructura y cuando la utilizo, es necesario que todos los archivos estén abiertos simultáneamente para actualizar datos, ya que en caso contrario da error #REF. Precisamente lo que pretendo evitar es tener varias decenas de archivos abiertos a la vez, cuando consulto esa hoja "resumen" de todos ellos. Saludos.

Luis Reyes 09 julio, 2014 20:51  

Hola Jorge Dunkelman, ¿crees que exista alguna forma de simplificar está formula?
Te agradezco mucho tu ayuda.

Saludos desde México

=SI.ERROR(BUSCARV($A12,'FCB Balanza'!$1:$3681,COINCIDIR(D$4,'FCB Balanza'!$A$1:$AO$1,0),0),0)+SI.ERROR(BUSCARV($A12,'CPB Balanza'!$A$1:$AO$3688,COINCIDIR(D$4,'CPB Balanza'!$A$1:$AQ$1,0),0),0)+SI.ERROR(BUSCARV($A12,'GPB Balanza'!$A$1:$AO$3682,COINCIDIR(D$4,'GPB Balanza'!$A$1:$AQ$1,0),0),0)+SI.ERROR(BUSCARV($A12,'R&D Balanza'!$1:$3682,COINCIDIR(D$4,'R&D Balanza'!$A$1:$AO$1,0),0),0)

Jorge Dunkelman 09 julio, 2014 22:41  

Luis,
mucho depende del contexto, por lo que te sugiero que me mandes el cuaderno.

Jorge Dunkelman 10 julio, 2014 08:11  

Luis, ponte en contacto conmigo por mail privado (figura en el enlace Ayuda, en la parte superior del blog).
Por lo que veo, quieres sumar los valores de las distintas hojas de acuerdo al mes y tipo de prueba. Hay mejores soluciones, como por ejemplo Datos-Consolidar.
Además, en tu ejemplo, el total de la Hoja1 no coincide con los totales de las hojas "uno", "dos", "tres" y "cuatro".

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP