¿Alguna vez necesitó extraer un valor de un intervalo en función de un valor coincidente en su lista? Por ejemplo, es posible que quiera extraer de forma dinámica el precio de un producto en función de la Id. del producto.
Si el conjunto de datos contiene la Id. del producto, puede usar una fórmula para obtener el precio.
Con este conjunto de fórmulas establecido como fórmulas de columna, cada fila (y cualquier fila recientemente agregada) mostrará el precio correspondiente al conjunto de datos del producto.
Diferentes enfoques en cuanto a esta fórmula
Se pueden usar tres métodos para extraer datos de un intervalo en función de un valor de búsqueda coincidente:
- VLOOKUP
- INDEX(MATCH())
- INDEX(COLLECT())
Analizaremos cómo usar cada una de ellas y debatiremos las ventajas y desventajas de cada enfoque.
VLOOKUP
La fórmula VLOOKUP busca un valor y da como resultado otro valor que se encuentra en la misma fila, pero en diferente columna (especificada). A continuación, se muestra el formato de una fórmula VLOOKUP:
=VLOOKUP([Valor de búsqueda], [Conjunto de datos que se busca], [Número de columna en el conjunto de datos],[Falso o verdadero según las necesidades de coincidencia exactas])
Para obtener el precio del ejemplo anterior mediante VLOOKUP, la fórmula sería la siguiente:
=VLOOKUP([Id. de producto asociado]@row, {Product Data | Product}, 4, false)
El intervalo de referencia entre hojas {Product Data | Product} se ve de la siguiente manera:
La fórmula arroja los valores en la columna Price (Precio) de la siguiente manera:
CONSEJO: Ajuste la fórmula con IFERROR para resolver los casos en los que no se encuentre una coincidencia en el conjunto de datos que se está buscando. En este ejemplo, la fórmula sería la siguiente:
=IFERROR(VLOOKUP([Id. de producto asociado]@row, {Product Data | Product}, 4, falso), "No Match Found")
Ventajas:
- Es una fórmula simple y más rápida.
Desventajas:
- Requiere que el valor de búsqueda esté en la primera columna del conjunto de datos que se busca.
- No se pueden extraer los valores a la izquierda de la columna del valor de búsqueda.
- La fórmula se corrompe en los siguientes casos: si se agrega una columna, si se elimina una columna entre el valor de búsqueda y la columna que se está extrayendo, o si se modifica el orden de las columnas.
INDEX(MATCH())
La fórmula INDEX(MATCH()) busca en un intervalo y extrae el valor que coincide con los criterios especificados. A continuación, se muestra el formato de una fórmula INDEX(MATCH()):
=INDEX([Intervalo con el valor que se arrojará],MATCH([Valor de búsqueda],[Intervalo con el valor que se busca],[0, 1 o -1, según el tipo de búsqueda]))
Para obtener el precio del ejemplo anterior mediante INDEX(MATCH()), la fórmula sería la siguiente:
=IFERROR(INDEX({Product Data | Price}, MATCH([Id. de producto asociado]@row, {Product Data | Product ID}, 0)), "No Match Found")
El intervalo de referencia entre hojas {Product Data | Price} se ve de la siguiente manera:
El intervalo de referencia entre hojas {Product Data | Product ID} se ve de la siguiente manera:
Como se mencionó anteriormente, también ajustamos la función INDEX(MATCH()) con una fórmula IFERROR para que se muestre la leyenda “No Match Found” (“No se encontró coincidencia”) si no se encuentra una Id. de producto coincidente para la fila, por lo que la fórmula arroja los valores en la columna Price (Precio) de la siguiente manera:
Ventajas:
- Permite realizar cambios en el orden de las columnas o eliminar columnas no utilizadas sin corromperse.
- Se pueden extraer valores de las columnas a la izquierda o la derecha del intervalo de valores de búsqueda.
- Se obtiene un resultado más rápido para conjuntos de datos más grandes.
- INDEX(MATCH(),MATCH()) se puede utilizar para establecer coincidencias de forma dinámica entre columnas y filas.
- La cantidad total de celdas a las que se hace referencia suele ser menor, lo que permite mantenerse por debajo del límite de 100 000 celdas en referencias cruzadas entre hojas.
Desventajas:
- Requiere más de una referencia cruzada entre hojas para los casos en los que existen datos de referencia en una hoja separada.
- Se limita a un solo criterio de coincidencia.
INDEX(COLLECT())
La fórmula INDEX(COLLECT()) busca en un intervalo y extrae el valor que coincide con uno o más de los criterios especificados. A continuación, se muestra el formato de la fórmula INDEX(COLLECT()):
=INDEX(COLLECT([Intervalo con el valor que se arrojará],[Intervalo con criterio],[Criterio],[Intervalo 2 con criterio],[Criterio], etc.),[1 para que arroje el índice de la fila])
Para obtener el precio del ejemplo anterior mediante INDEX(COLLECT()), la fórmula sería la siguiente:
=IFERROR(INDEX(COLLECT({Product Data | Price}, {Product Data | Product ID}, [Id.de producto asociado]@row), 1), "No Match Found")
El intervalo de referencia entre hojas {Product Data | Price} se ve de la siguiente manera:
El intervalo de referencia entre hojas {Product Data | Product ID} se ve de la siguiente manera:
Como se mencionó anteriormente, también ajustamos la función INDEX(COLLECT()) con una fórmula IFERROR para que se muestre la leyenda “No Match Found” (“No se encontró coincidencia”) si no se encuentra una Id. de producto coincidente para la fila, por lo que la fórmula arroja los valores en la columna Price (Precio) de la siguiente manera:
Ventajas:
- Permite realizar cambios en el orden de las columnas o eliminar columnas no utilizadas sin corromperse.
- Se pueden extraer valores de las columnas a la izquierda o la derecha del intervalo de valores de búsqueda.
- Por lo general, el resultado se obtiene de forma más rápida que con VLOOKUP, pero podría ser más lento que con INDEX/MATCH.
- Permite el uso de varios criterios dentro de la fórmula COLLECT para establecer coincidencias en múltiples columnas o crear criterios más complejos.
- Permite obtener la segunda, la tercera y más coincidencias reemplazando “, 1” al final de la fórmula en lugar de siempre mostrar la primera coincidencia.
Desventajas:
- Requiere más de una referencia cruzada entre hojas para los casos en los que existen datos de referencia en una hoja separada.
- Puede ser más lento que usar una fórmula INDEX/MATCH, en especial, si se utilizan varios criterios.
¿Aún necesita ayuda?
Utilice la plantilla Manual de fórmulas para obtener más recursos de soporte y ver más de 100 fórmulas, incluidos un glosario de cada función, con las que puede practicar en tiempo real, y ejemplos de fórmulas comunes y avanzadas.
Vea ejemplos de cómo otros clientes de Smartsheet usan esta función o haga preguntas sobre su caso de uso específico en la Comunidad de Smartsheet.
Pregúntele a la Comunidad