() translation by (you can also view the original English article)
Cuando necesites encontrar y extraer una columna de datos de una tabla y colocarla en otra, usa la función VLOOKUP. Esta función está presente en cualquier versión de Excel en Windows y Mac, así como en Google Sheets. Te permite encontrar datos en una tabla usando algún tipo de identificador que tenga en común con otra. Las dos tablas pueden estar en diferentes hoja o incluso en diferentes libros. Hay también una función HLOOKUP, que hace lo mismo pero con datos distribuídos de forma horizontal, en filas.
Las funciones MATCH e INDEX son buenas para usar cuando usted se está conectando con la ubicación de un dato específico tales como la columna o la fila que contiene el nombre de una persona.
Opciones Premium
Antes de que comencemos a examinar las funciones de Excel, ¿sabía que Envato Market tiene una variedad de scripts y plugins para Excel que le permiten realizar funciones avanzadas?
Por ejemplo, usted puede:
- exportar datos de WordPress hacia Excel
- poner y sacar datos desde Excel usando una clse PHP
- convertir una hoja de cálculo de Excel en una tabla HTML responsibe-
- convertir archivos Excel a .NET DataTable
- y mucho más
Presentación digital
Si quiere seguir todo el tutorial usando su propio archivo Excel, entonces hágalo. O si lo prefiere, descargue el archivo zip incluido para este tutorial, el cual contiene una muestra de un libro de trabajo llamado vlookup example.xlsx.
Usando VLOOKUP
Cuando VLOOKUP encuentra el identificador que usted especifica en la fuente de datos, entonces puede encontrar cualquier celda en esa fila y devolverle la información a usted. Note que en el fuente de dato, el identificador debe estar en la primera columna de la tabla.
Sintaxis
La sintaxis de la función VLOOKUP es:
=VLOOKUP(lookup value, table range, column number, [true/false])
Aquí está lo que estos argumentos significan:
- Valor Lookup. La celda que tiene el único identificador.
- Rango de la tabla. El rango de las celdas que tiene el identificador en la primera columna, seguido por el resto de los datos en las otras columnas.
- Número de columnas. El número de la columna que tiene el dato que está buscando. No se confunda con las letras de la columna. En la ilustración de arriba, la condición está en 4 columnas.
- True/False. Este argumento es opcional. True significa que la correspondencia aproximada es aceptable, y False significa que una coincidencia exacta es aceptable.
Queremos encontrar la cantidad de ventas desde la tabla en la ilustración arriba, así que usamos tres argumentos:
Defina un Nombre de Rango para Crear una Referencia Absoluta
En Vlookup example.xlsx, mire la hoja de calculo Sales Amounts. Introduciremos la fórmula en B5, luego usamos la característica AutoFIll para copiar la fórmula abajo en la hoja. Eso significa que el rango de la tabla en la fórmula tiene que ser una referencia absoluta. Una buena manera de hacer eso es definir el nombre para el rango de la tabla.
Definiendo un Nombre de Rango en Excel
- Antes de introducir la fórmula, vaya a la fuente de datos de la hoja de cálculo.
- Seleccione todas las celdas, desde la A4 (el encabezado para el Orden # columna) hacia abajo hasta H203. Una manera rápida de hacer esto es pulsando A4, luego presionar Ctrl+Shift+End (Command+Shift+End en la Mac).
- Haga clic dentro del Name Box arriba de la columna A (ahora Name Box muestra A4).
- Escriba data, después presione Enter.
- Ahora puede usar el nombre data en la formula en vez de $A$4:$H$203.
Definiendo un nombre de rango en Google Sheets.
En Google Sheets, definir un nombre es un poco diferente.
- Haga clic en el encabezado de la primer columna de su fuente de datos, luego presione Ctrl+Shift+Flecha derecha (Command+Shift+Flecha derecha en la Mac). Eso seleccione la fila de los encabezados de columnas.
- Presione Ctrl-Shift-Flecha abajo (Command-Shift-Flecha abajo en la Mac). Eso seleccione el dato actual.
- Haga clic en el menú Data, luego seleccione Named and protected ranges.
- En Named and protected ranges a la derecha, escriba data, luego pulse Done.
Introduciendo la Formula
Para introducir la formula, vaya a la hoja de cálculo Sales Amount y haga clic en B5.
Introduzca la formula:
=VLOOKUP(A5,data,8,FALSE)
Presione Enter.
El resultado debería de ser 40. Para rellenar en los valores inferiores de la columna, haga clic de vuelta sobre B5, si es necesario. Ponga el puntero del sobre el punto AutoFill en celda de la esquina inferior derecha, para que el puntero ser convierta en una cruz.
Doble clic para rellenar los valores de la columna.
Si lo desea, puede ejecutar la función VLOOKUP en las siguientes columnas para extraer otros campos, como el nombre o el estado.
Usando MATCH
La función MATCH no le devuelve el valor de dato a usted, usted provee el valor que está buscando, y la función devuelve la posición de ese valor. Es como preguntar donde está la Calle Main #135, y conseguir la respuesta; está en el 4to. edificio por la calle.
Sintaxis
La sintaxis de la función MATCH es:
=MATCH(lookup value, table range, [match type])
Los argumentos son:
- Valor Lookup. La celda que tiene el identificador único.
- Rango de tabla. El rango de las celdas que está buscando.
- Tipo de Coincidencia. Opcional. Es cómo usted específica cuán cerca de una coincidencia usted quiere, así como sigue:
| Después el valor más alto | -1 | Los valores deben ser en orden descendente. |
| Valor Target | 0 | Estos valores pueden ser en cualquier orden. |
| Los siguientes valores más bajos | 1 | Tipo por defecto. Los valores deben estar en orden ascendente. |
Al igual como con la función VLOOKUP, usted probablemente encontrará que la función MATCH es más fácil de usar si aplica un nombre de rango. Vaya a la Fuente de Datos de la hoja, seleccione desde B4 (la columna encabezado para orden #) hasta el fondo, clic en Name box arriba de la columna A, y llámela order_number. Note que los valores están en orden ascendente.
Vaya a la pestaña Match de la hoja de cálculo. En B5, introduzca la función MATCH:
=MATCH(A5,order_number,1)
Si no ha definido un nombre de rango, entonces ha escrito una función así:
=MATCH(A5,'Source Data'!A5:A203,0)
De cualquier manera, puede ver que ésta está en la posición 14 (haciéndola en el orden 13).
Usando INDEX
La función INDEX es lo opuesto a la función MATHC y es similar a la función VLOOKUP. Usted le dice a la función cual es la fila y columna de datos que quiere y le dirá el valor de lo que está en la celda.
Sintaxis
La sintaxis de la función INDEX es:
=INDEX(data range, row number, [column number])
Los argumentos son:
- Rango Data. Al igual que en las otras dos funciones, esta es la tabla de datos.
- Número de Filas. El número de filas de los datos, el cual no es necesariamente la fila de la hoja de cálculo. Si el rango de la tabla empieza en la fila 10 de la hoja, entonces esa fila es #1.
- Número de columna. El número de la columna del rango de datos. Si el rango empieza sobre la columna E, esa columna es #1.
La documentación de Excel le dirá que el argumento; número de columna, es opcional, pero el número de la fila es de tipo opcional, también. Si el rango de la tabla tiene sólo una fila o una columna, entonces no tiene que usar otro argumento.
Vaya a la hoja Index del libro de trabajo y haga clic en C6. Primero, queremos encontrar lo que está contenido en la fila 9, columna 3 de la tabla. En la formula, usaremos el nombre del rango que creamos anteriormente.
Introduzca la formula:
=INDEX(data,A6,B6)
Esta devuelve el último nombre del cliente: Strevell. Cambie los valores de A6 y B6, y el resultado en C6 mostrará diferentes resultados (observe que muchas filas tiene el mismo estado y nombres de productos).
En conclusión
La habilidad de una hoja de cálculo para ver en otra hoja de cálculo y extraer datos es una herramienta magnífica. De esta manera, usted puede tener una hoja que contiene todos los datos que usted necesita para muchos propósitos, luego extraer lo que usted necesita para instancias específicas.
