¿Cómo buscar verticalmente y obtener el último valor coincidente en Excel?
La función VLOOKUP de Excel es una de las herramientas más utilizadas para buscar datos en tablas según un criterio específico. Sin embargo, de forma predeterminada, VLOOKUP solo recupera el primer valor que coincide con los criterios de búsqueda dentro de un conjunto de datos. Este comportamiento se convierte en una limitación cuando sus datos contienen valores de búsqueda repetidos y necesita extraer la última aparición de una entrada coincidente. Esta necesidad surge con frecuencia en escenarios como el seguimiento del estado más reciente, la obtención de las últimas ventas de un cliente o la identificación de la última entrada registrada en listas cronológicas. Para superar esta limitación, Excel ofrece varios enfoques alternativos que combinan funciones como LOOKUP, XLOOKUP, ÍNDICE y COINCIDIR, así como soluciones intuitivas de terceros como Kutools para Excel. En este artículo, exploraremos cómo funciona cada método, analizaremos sus aplicaciones prácticas, destacaremos sus ventajas y limitaciones, y le proporcionaremos consejos operativos para realizar con facilidad una búsqueda vertical que devuelva el último valor coincidente.
Buscar verticalmente y devolver el último valor coincidente en Excel
Buscar verticalmente y devolver el último valor coincidente con la función BUSCAR
Aunque VLOOKUP no puede encontrar directamente la última coincidencia, la función BUSCAR (LOOKUP) ofrece una solución ingeniosa. Este enfoque resulta especialmente útil cuando su conjunto de datos no está ordenado y necesita una fórmula compatible con prácticamente todas las versiones de Excel. La fórmula aprovecha la manera en que BUSCAR gestiona matrices y errores para localizar rápidamente la última aparición.
Para extraer el último valor coincidente mediante LOOKUP, siga estos pasos:
1. Seleccione la celda donde desea mostrar el último valor coincidente e introduzca la siguiente fórmula:
=LOOKUP(2,1/($A$2:$A$12=E2),$C$2:$C$12)2. Pulse Intro. Si necesita aplicar la fórmula a filas adicionales, arrastre el controlador de relleno hasta el rango deseado. Así podrá realizar búsquedas del último valor coincidente para varios valores de búsqueda sin esfuerzo.
$A$2:$A$12es la columna de búsqueda (criterios).E2es la celda que contiene el valor que se va a buscar.$C$2:$C$12es la columna de devolución (resultados).
1/($A$2:$A$12=E2)genera una matriz con un valor de 1 donde la condición es verdadera, y errores#DIV/0!en el resto.LOOKUP(2, ...)aprovecha el hecho de que LOOKUP ignora los errores y busca el número 2 (que no está presente). Así, LOOKUP encuentra la última aparición de 1 en la matriz y devuelve el valor correspondiente de la matriz de resultados, obteniendo así el último valor coincidente.
Consejos y notas:
- Asegúrese de que los rangos de búsqueda y de resultados tengan el mismo tamaño y utilice referencias absolutas al rellenar hacia abajo.
- Si la matriz de búsqueda contiene celdas vacías o errores, los resultados pueden verse afectados. Limpia los datos o envuélvelos con
IFERRORsegún sea necesario. - Si recibe
#N/A, asegúrese de que el valor de búsqueda exista realmente en el rango de origen.
Ventajas: Funciona en todas las versiones de Excel y evita la necesidad de introducir fórmulas matriciales de forma especial.
Limitaciones:Es menos robusto si hay celdas vacías o valores de error en la matriz de búsqueda; además, no ofrece salidas personalizadas ante errores sin utilizar funciones envolventes (p. ej.,)SI.ERROR).
Buscar verticalmente y devolver el último valor coincidente con Kutools para Excel
Kutools para Excel ofrece una forma intuitiva y eficiente de obtener el último valor coincidente, ideal para usuarios que prefieren un enfoque visual sin fórmulas o necesitan procesar grandes volúmenes de datos rápidamente. La herramienta integrada Buscar de abajo hacia arriba del conjunto Super BUSCARV de Kutools elimina la complejidad de las fórmulas, gestiona automáticamente los errores y puede mostrar resultados en varias celdas a la vez, ahorrándole tiempo y reduciendo errores de introducción. Este método es especialmente adecuado para usuarios que no dominan las funciones avanzadas de Excel o desean minimizar la edición manual de fórmulas.
Tras instalar Kutools para Excel, siga estos pasos:
1. Haga clic en Kutools > Super BUSCARV > Buscar de abajo hacia arriba. Vea la captura de pantalla:
2.En el cuadro de diálogo Buscar de abajo hacia arriba:
- Seleccione las celdas del valor de búsqueda y las celdas de destino en las secciones “Área de ubicación de la lista” y “Rango de valor de búsqueda”.
- Especifique el rango correspondiente en la sección «Rango de datos». Asegúrese de que todos los rangos tengan el mismo tamaño y estén en el mismo orden para evitar desalineaciones.
- Haga clic en Aceptar para aplicar.
Una vez ejecutado, Kutools devolverá instantáneamente los últimos elementos coincidentes, como se muestra a continuación:
Consejo: Si desea mostrar un mensaje personalizado en lugar de #N/A cuando no haya coincidencias, haga clic en Opciones, marque Reemplaza el resultado de salida que no se encuentra y devuelve '#N/A' con el valor especificado e introduzca el texto deseado.
Ventajas: No requiere editar fórmulas. Permite operaciones masivas y la sustitución de errores, lo que lo hace ideal para principiantes en Excel y perfecto para tareas con grandes volúmenes de datos.
Desventajas: Requiere tener instalado el complemento Kutools; la función solo está disponible en las versiones completa o de prueba de Kutools.
Consejo práctico: Revise siempre la salida y el rango de entrada antes de confirmar para garantizar la precisión de los resultados, especialmente si sus datos cambian dinámicamente.
Buscar verticalmente y devolver el último valor coincidente con las funciones ÍNDICE y COINCIDIR
La combinación de las funciones ÍNDICE y COINCIDIR ofrece un método versátil y compatible entre versiones para buscar verticalmente el último valor coincidente en Excel. Este enfoque es altamente adaptable, no exige que los datos estén ordenados y funciona en todas las versiones de Excel, incluidas las más antiguas. No obstante, según su versión de Excel, es posible que deba introducir la fórmula como una fórmula matricial para obtener resultados correctos.
Para utilizar este método, siga estos pasos:
1.En la celda de destino, introduzca la siguiente fórmula:
=INDEX($C$2:$C$12,MATCH(2,1/($A$2:$A$12=E2)))2.Confirme la fórmula:
- En Excel 2019 o versiones anteriores, finalice con Ctrl + Mayús + Intro (Excel añadirá automáticamente las llaves {}).
- En Microsoft 365 / Excel 2021 y versiones posteriores, simplemente pulse Intro.
3. Si tiene varios Rangos de valor de búsqueda, arrastre el controlador de relleno hacia abajo para aplicar la fórmula a las filas adyacentes y procesarlas por lotes.
$A$2:$A$12es la columna de búsqueda (criterios).E2es la celda que contiene el valor que se va a buscar.$C$2:$C$12es la columna de devolución (resultados).
1/($A$2:$A$12=E2)genera una matriz con un valor de 1 donde la condición de búsqueda es verdadera, y errores#DIV/0!en el resto. Así se convierten los valores lógicos VERDADERO/FALSO en señales numéricas.MATCH(2, 1/($A$2:$A$12=E2))le pide a Excel que busque el número 2 (que no está presente). Como resultado, MATCH devuelve la posición de la última aparición de 1 en la matriz, es decir, la última coincidencia verdadera.INDEX($C$2:$C$12, ...)utiliza esa posición para obtener el valor correspondiente del rango de resultados.
Consejos y recomendaciones:
- Asegúrese de que los rangos de búsqueda y de resultados tengan el mismo número de filas y use referencias absolutas al rellenar hacia abajo.
- Si ve
#N/Ao#DIV/0!, compruebe si hay claves sin coincidencia, celdas vacías o errores en la matriz de búsqueda. Para obtener una salida más limpia, envuélvalo conIFERROR, por ejemplo:=IFERROR(your_formula, "").
Ventajas: Versátil y compatible con versiones anteriores de todas las ediciones de Excel.
Inconvenientes: Es ligeramente más difícil de recordar y requiere entrada matricial en versiones antiguas de Excel.
Buscar verticalmente y devolver el último valor coincidente con la función XLOOKUP
La función XLOOKUP, disponible en Excel 365, Excel 2021 y versiones posteriores, ofrece la solución más sencilla y moderna para devolver la última coincidencia. Gracias a sus parámetros que controlan la dirección de la búsqueda y el manejo de errores, XLOOKUP puede buscar de abajo hacia arriba y recuperar el último valor coincidente sin necesidad de fórmulas matriciales heredadas complicadas.
Para usar XLOOKUP y obtener el último valor coincidente:
1. En la celda de destino, introduce la siguiente fórmula. A continuación, arrastra el controlador de relleno si la necesitas para rangos de valores de búsqueda adicionales.
=XLOOKUP(E2, $A$2:$A$12, $C$2:$C$12, , , -1)
E2: el valor que se busca.$A$2:$A$12: el rango en el que se realiza la búsqueda.$C$2:$C$12: la matriz de resultados., ,: las dos comas indican que los argumentos opcionalesif_not_foundymatch_modese omiten (se usan los valores predeterminados).-1:search_mode=-1busca de último a primero (de abajo hacia arriba), por lo que obtiene la última coincidencia.
Notas prácticas:
- No se requiere ninguna entrada especial de matriz. Puede proporcionar un mensaje personalizado mediante
if_not_foundsi es necesario. - XLOOKUP devuelve la última coincidencia según el orden de la matriz proporcionada (de abajo hacia arriba con)
-1), independientemente de si el filtro está visible o no. - Para procesamiento masivo, rellene hacia abajo; cada fila se evalúa de forma independiente.
Ventajas: Sintaxis sencilla; búsqueda integrada de último a primero; sin fórmulas matriciales heredadas.
Limitación: Solo disponible en Excel 365, Excel 2021 y versiones posteriores.
Buscar verticalmente y devolver el último valor coincidente con una macro de VBA
En algunos casos, especialmente si necesita automatizar el proceso de búsqueda o trabajar con conjuntos de datos muy grandes, utilizar una macro de VBA puede ser una solución práctica. VBA le permite crear un script personalizado con la lógica de búsqueda y gestionar excepciones o condiciones especiales que las fórmulas no pueden abordar fácilmente.
Escenario aplicable: Priorice esta solución si necesita ejecutar con frecuencia la misma operación de búsqueda en distintos libros de trabajo o desea encapsular su lógica en un script reutilizable.
1. Haga clic en Programador > Visual Basic para abrir la ventana de Microsoft Visual Basic para Aplicaciones. A continuación, haga clic en Insertar > Módulo y pegue el siguiente código en el módulo:
Option Explicit
Sub FindLastMatch()
Dim searchRange As Range
Dim returnRange As Range
Dim searchValue As Variant
Dim i As Long
Dim foundValue As Variant
Dim found As Boolean
Const xTitleId As String = "KutoolsforExcel"
' Get ranges and value from user
On Error GoTo CleanFail
Set searchRange = Application.InputBox("Select the lookup column (single column):", xTitleId, Type:=8)
If TypeName(searchRange) = "Boolean" Then Exit Sub ' Cancel pressed
Set returnRange = Application.InputBox("Select the return column (single column):", xTitleId, Type:=8)
If TypeName(returnRange) = "Boolean" Then Exit Sub ' Cancel pressed
searchValue = Application.InputBox("Enter the lookup value:", xTitleId, Type:=2)
If VarType(searchValue) = vbBoolean And searchValue = False Then Exit Sub ' Cancel pressed
' Basic validations
If searchRange.Columns.Count <> 1 Or returnRange.Columns.Count <> 1 Then
MsgBox "Please select a single column for both lookup and return ranges.", vbExclamation
Exit Sub
End If
If searchRange.Rows.Count <> returnRange.Rows.Count Then
MsgBox "Lookup and return ranges must have the same number of rows.", vbExclamation
Exit Sub
End If
If Not searchRange.Parent Is returnRange.Parent Then
MsgBox "Lookup and return ranges must be on the same worksheet.", vbExclamation
Exit Sub
End If
' Scan from bottom to top
found = False
For i = searchRange.Rows.Count To 1 Step -1
If CStr(searchRange.Cells(i, 1).Value) = CStr(searchValue) Then
foundValue = returnRange.Cells(i, 1).Value
found = True
Exit For
End If
Next i
If found Then
MsgBox "The last matching value is: " & foundValue, vbInformation
Else
MsgBox "No match found.", vbInformation
End If
Exit Sub
CleanFail:
MsgBox "Operation cancelled or invalid selection.", vbExclamation
End Sub
2. Haga clic en el botón 
para ejecutar el código. En los cuadros de diálogo que aparecen, seleccione la columna de búsqueda, la columna de devolución e introduzca su valor de búsqueda según se le indique. La macro escanea desde la última fila hacia arriba y muestra el último valor coincidente encontrado.
Notas:
- Asegúrese de que los rangos de búsqueda y de resultados sean columnas individuales, tengan el mismo número de filas y estén ubicados en la misma hoja de cálculo.
- La macro compara los valores como texto por simplicidad. Si necesita distinguir formatos numéricos (por ejemplo, 00123 frente a 123), ajuste la lógica de comparación en consecuencia.
- Si no se encuentra ninguna coincidencia o la selección no es válida/se cancela, se muestra una notificación.
Ventajas: Totalmente automatizado, reutilizable y sin necesidad de introducir ni copiar fórmulas en las celdas.
Desventajas: Configuración inicial ligeramente más compleja; requiere un libro habilitado para macros (.xlsm) y un entorno con macros de confianza.
Devolver el último valor coincidente en Excel es un requisito habitual, ya sea para supervisar las últimas transacciones, analizar actualizaciones o realizar un seguimiento de cambios a lo largo del tiempo. Con los métodos descritos anteriormente, podrá elegir el enfoque adecuado en función de su versión de Excel, sus preferencias de flujo de trabajo y su familiaridad con el conjunto de herramientas de Excel. Los enfoques mediante BUSCAR, ÍNDICE y COINCIDIR, XLOOKUP, Kutools y macros de VBA tienen cada uno sus propias fortalezas y escenarios de uso óptimos.
Resolución de problemas: Si su fórmula devuelve un error #N/A o #¡DIV/0!, compruebe que la selección del rango es correcta, asegúrese de que el valor buscado exista y confirme que sus rangos estén correctamente alineados. Evite las celdas vacías en su columna de búsqueda siempre que sea posible para garantizar la fiabilidad de las fórmulas. En caso de duda, pruebe la fórmula con una muestra reducida de sus datos para verificar la configuración; esto le ayudará a aislar posibles errores.
Para profundizar más en técnicas de búsqueda —como recuperar múltiples resultados, concatenar coincidencias o buscar en distintas hojas de cálculo—, considere visitar recursos especializados como nuestra página de tutoriales de Excel con una amplia variedad de artículos relacionados y guías paso a paso. Dominar estas habilidades de búsqueda le ayudará a ganar confianza para procesar, analizar e informar sobre sus datos críticos en Excel de forma más eficaz.
Más artículos relacionados:
- Buscar valores verticalmente en varias hojas de cálculo
- En Excel, podemos aplicar fácilmente la función BUSCARV para obtener valores coincidentes en una única tabla dentro de una hoja de cálculo. Pero, ¿alguna vez se ha preguntado cómo buscar un valor verticalmente en varias hojas a la vez? Supongamos que tiene las tres hojas siguientes con sus respectivos rangos de datos y desea recuperar algunos valores correspondientes según criterios aplicables a esas tres hojas.
- Uso de BUSCARV con coincidencia exacta y aproximada en Excel
- En Excel, BUSCARV es una de las funciones más importantes para buscar un valor en la columna más a la izquierda de una tabla y devolver el valor correspondiente de la misma fila en un rango especificado. ¿Está aplicando correctamente la función BUSCARV en Excel? En este artículo le explicaré cómo utilizarla.
- BUSCARV y devolver una celda vacía o un valor específico en lugar de 0 o #N/A
- Normalmente, al usar la función BUSCARV para obtener un valor correspondiente, si la celda coincidente está vacía, la fórmula devuelve 0; y si el valor buscado no se encuentra, aparece el error #N/A, como se muestra en la siguiente captura de pantalla. En lugar de mostrar 0 o #N/A, ¿cómo puede hacer que aparezca una celda vacía u otro texto específico?
- BUSCARV y devolver toda la fila / Fila completa de un valor coincidente en Excel
- Normalmente, puede usar BUSCARV para obtener un valor coincidente de un rango de datos, pero ¿ha intentado alguna vez encontrar y devolver toda la fila de datos según criterios específicos, como se muestra en la siguiente captura de pantalla?
- BUSCARV y concatenar varios valores correspondientes en Excel
- Como todos sabemos, la función BUSCARV en Excel nos permite buscar un valor y devolver los datos correspondientes de otra columna, pero normalmente solo obtiene el primer valor relacionado si hay varios datos coincidentes. En este artículo explicaré cómo buscar verticalmente y concatenar varios valores correspondientes en una sola celda o en una lista vertical.
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos