Búsqueda y referencia en Excel: guía para empezar

Las funciones de búsqueda y referencia permiten localizar un dato en un catálogo y devolver información relacionada sin duplicarla. En este tutorial usarás un libro de ventas con códigos, productos y precios para aprender a buscar hacia abajo, hacia arriba y hacia la izquierda. También verás cómo fijar referencias con F4 y cómo elegir la función adecuada. Las funciones clásicas funcionan en versiones antiguas de Excel; BUSCARX y las funciones de matriz requieren Excel 2021, 2024 o Microsoft 365.

Qué son las funciones de búsqueda y referencia

Las funciones de búsqueda y referencia resuelven dos tareas relacionadas. Buscar significa localizar un código, nombre o categoría. Referenciar significa conectar ese dato con una celda, un rango o incluso otra hoja.

En el libro de este tutorial, la hoja Catálogo contiene códigos en A2:A5, productos en B2:B5 y precios en C2:C5:

Si otra hoja necesita el precio de «Monitor», puedes escribir su código y devolver el precio mediante una fórmula. Así, el catálogo conserva cada dato una sola vez y los pedidos pueden traer el precio correcto.

Según la ayuda oficial de Microsoft sobre funciones de búsqueda y referencia, estas funciones pueden trabajar con rangos, tablas y matrices. No todas se llaman igual ni están disponibles en todas las versiones.

BUSCARV (VLOOKUP): cómo localizar un dato en una columna

BUSCARV (VLOOKUP) busca un valor en la primera columna de una matriz y devuelve un valor de la misma fila.

=BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;[ordenado])

• valor_buscado: el código que quieres encontrar.
• matriz_buscar_en: las columnas que contienen la clave y el resultado.
• indicador_columnas: qué columna contiene el resultado, contando desde la primera del rango.
• ordenado: FALSO exige coincidencia exacta; no debe omitirse en estos ejemplos.

En la hoja Pedidos, escribe «Monitor» en E2 y utiliza:

=BUSCARV(E2;Catálogo!A2:C5;3;FALSO)

El resultado es 189,00 euros.

Si copias la fórmula hacia abajo, E2 debe contener el código o nombre que se desea consultar.

BUSCARH (HLOOKUP): buscar un dato en la fila superior

BUSCARH (HLOOKUP) busca en la primera fila de una matriz y devuelve un valor situado en la misma columna. Su sintaxis es:

=BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas;[ordenado])

En una hoja Resumen mensual, los meses ocupan B1:M1 y las ventas B2:M2. Para obtener las ventas de marzo:

=BUSCARH("Mar";B1:M2;2;FALSO)

BUSCARX (XLOOKUP): buscar hacia cualquier dirección

BUSCARX (XLOOKUP) devuelve el valor de una columna separada por la columna donde aparece la clave. Puede buscar hacia arriba, abajo, izquierda o derecha y utiliza coincidencia exacta por defecto.

=BUSCARX(valor_buscado;matriz_buscada;matriz_resultado;[si_no_encontrado];[modo_búsqueda];[modo_coincidencia])

Para el catálogo:

=BUSCARX(E2;Catálogo!B2:B5;Catálogo!C2:C5)

Si E2 contiene «Monitor», devuelve 189,00 euros.

BUSCARX está disponible desde Excel 2021, Excel 2024, Microsoft 365 y Excel para la web. En Excel 2019 o 2016, utiliza BUSCARV o INDICE y COINCIDIR.

INDICE y COINCIDIR: buscar sin depender del número de columna

INDICE (INDEX) selecciona un valor usando números de fila y columna. COINCIDIR (MATCH) busca un valor y devuelve su posición dentro de un rango. La combinación permite buscar sin Numerar columnas ni depender de que la clave esté en la primera.

=INDICE(C2:C6;COINCIDIR(E2;A2:A6;0))

COINCIDIR devuelve la posición dentro del rango, no el número completo de la hoja. Por eso INDICE recibe C2:C6 como rango de resultados.

Qué cambia al copiar referencias en Excel

Una referencia indica qué celdas usa una fórmula. Al copiarla, Excel puede mover esa referencia, mantenerla fija o fijar solo una parte.

En =B2*$F$1, B2 corresponde al precio que sí debe cambiar por fila. $F$1 contiene el tipo de IVA, que debe ser el mismo en todas las fórmulas.

Fijar una referencia con F4 mientras escribes la fórmula

1. Selecciona C2 y escribe =B2*.
2. Selecciona F1 manteniendo pulsado Ctrl.
3. Pulsa F4.
4. Completa la fórmula con *F1 y pulsa Entrar.

=B2*$F$1

Con el cursor sobre una referencia, F4 alterna el orden:

F1
$F$1
F$1
$F1
F1

En Mac, utiliza Cmd+T. En portátiles puede ser necesario Fn+F4 si el brillo u otra función ocupa esa tecla.

Elegir una función para mover datos o referencias

Consultar una tabla sin repetir búsquedas limitadas

Creemos la hoja Ventas con cinco registros. La clave está en A, el producto en B, la categoría en C, las unidades en D y el importe en E.

1. Crea la hoja Ventas y copia la tabla anterior.
2. En G2, busca el código de B2 con BUSCARV:

=BUSCARV(B2;A2:E6;3;FALSO)

El resultado es Pantallas.

1. En H2, busca hacia la derecha con BUSCARH. Para ello, organiza una copia horizontal de códigos e importes en G1:H5:

=BUSCARH(G1;$G$1:$H$5;2;FALSO)

1. En I2, usa BUSCARX para devolver la categoría sin depender de la columna:

=BUSCARX(B2;$B$2:$B$6;$C$2:$C$6)

1. En J2, aplica INDICE y COINCIDIR para recuperar el importe:

=INDICE($E$2:$E$6;COINCIDIR(B2;$A$2:$A$6;0))

Devolver varios resultados con FILTRAR

FILTRAR (FILTER) devuelve todas las filas que cumplen una condición. Como una fórmula de matriz dinámica, puede nombre en pantalla muestra varias filas.

=FILTRAR(A2:E6;C2:C6="Accesorios")

Obtener una lista sin duplicados con UNICOS

UNICOS (UNIQUE) elimina duplicados de un rango o matriz.

=UNICOS(C2:C6)

Ordenar referencias y resultados para el informe

ORDENAR POR (SORTBY) ordena un rango usando otro rango como criterio.

=ORDENAR POR(A2:E6;E2:E6;-1)

El último argumento -1 ordena de mayor a menor.

Controlar referencias dinámicas sin conocer la ruta exacta

DESREF (OFFSET) desplaza una referencia. INDIRECTO (INDIRECT) interpreta como referencia una dirección escrita como texto. Una tabla de Excel utiliza referencias estructuradas que crecerán con sus filas.

DESREF e INDIRECTO son volátiles: Excel vuelve a calcularlas cuando cambia el libro. En libros grandes, usarlas con frecuencia puede reducir el rendimiento.

Calcular un rango shifted con DESREF

DESREF parte de una referencia, la desplaza y, opcionalmente, define su tamaño. Esta fórmula suma tres celdas que comienzan una fila debajo de B1:

=SUMA(DESREF(B1;1;0;3;1))

En la hoja Semanas, B2:B4 contiene 120, 135 y 145. La suma es 400.

Construir una dirección de hoja con INDIRECTO

INDIRECTO permite concatenar el nombre de una hoja con una dirección fija. En Resumen, escribe «Enero» en A1:

=SUMA(INDIRECTO("'"&A1&"'!B2:B10"))

La hoja Enero contiene 1.250, 1.480, 1.620 y 510 en B2:B5; las demás celdas del rango están vacías. Suman 4.860.

Por qué una búsqueda devuelve #N/A, #¡VALOR! o #¡REF!

Los tres errores tienen causas distintas. #N/A significa que no se encontró una coincidencia. #¡VALOR! aparece cuando una operación intenta usar texto. #¡REF! indica que la fórmula apunta a una celda que ya no existe.

Una celda vacía no produce #¡VALOR!: en una operación cuenta como 0. El error aparece si contiene texto, incluso un espacio.

Corregir un resultado incorrecto de BUSCARV

Si BUSCARV devuelve un valor que no corresponde, comprueba tres puntos:

1. El valor de valor_buscado debe coincidir exactamente con la primera columna.
2. matriz_buscar_en debe incluir tanto la columna de búsqueda como la del resultado.
3. Usa FALSO como cuarto argumento para exigir coincidencia exacta.

=BUSCARV(E2;Catálogo!A2:C5;3;FALSO)

En el catálogo, «Monitor» está en B2, no en A2. Si se busca «Monitor» contra A2:C5 con FALSO, el resultado es #N/A.

Para buscar por producto, utiliza B2:C5, pero recuerda que el indicador de columna cambia a 2:

=BUSCARV(E2;Catálogo!B2:C5;2;FALSO)

Si la búsqueda debe ser aproximada, hay que utilizar VERDADERO y ordenar la primera columna. Para códigos,keys y nombres de catálogo, la coincidencia exacta es la opción segura.

Evitar #¡REF! al cambiar la tabla

#¡REF! aparece cuando una fórmula apunta a una fila o columna eliminada. En BUSCARV también aparece si el indicador de columnas supera el número de columnas del rango. Por ejemplo, pedir la quinta columna en un rango de cuatro produce #¡REF!.

1. Pulsa Ctrl+Z para deshacer la eliminación.
2. Si no puedes deshacer, selecciona la celda con error.
3. Cambia la referencia para que incluya las columnas existentes.
4. Revisa el indicador de columnas.
5. Pulsa Entrar y comprueba el resultado.

=BUSCARV(E2;Catálogo!A2:C5;3;FALSO)

Si el catálogo se queda en A2:B5, la fórmula debe usar el indicador 2, no 3.

Qué funciones están disponibles según tu versión de Excel

La disponibilidad depende de la versión y, en Excel para la web o Mac, del canal de actualización. Las funciones clásicas mantienen una compatibilidad amplia; las dinámicas requieren una versión posterior.

Microsoft 365 se mantiene actualizado. Excel 2021, 2024, 2019, 2016, 2013, 2010, 2007 y 2003 son versiones existentes; no hay versiones 2012, 2017 ni 2018. La referencia oficial identifica las funciones nuevas mediante marcadores de versión.

Si una función no aparece, Excel muestra #¿NOMBRE?. Comprueba primero la versión y, después, que el nombre esté escrito en español.

Lo que ya sabes hacer con búsqueda y referencia

• Buscar un código en una columna con BUSCARV.
• Buscar un mes en la primera fila con BUSCARH.
• Buscar en cualquier dirección con BUSCARX.
• Combinar INDICE y COINCIDIR para buscar sin numerar columnas.
• Distinguir referencias relativas, absolutas y mixtas.
• Fijar una referencia con F4 mientras escribes una fórmula.
• Devolver varias filas con FILTRAR.
• Eliminar duplicados con UNICOS.
• Ordenar resultados con ORDENAR POR.
• Diferenciar #N/A, #¡VALOR! y #¡REF!.

Siguiente paso: automatizar fórmulas desde VBA

Range.Formula, FormulaLocal, FormulaR1C1 y FormulaR1C1Local escriben fórmulas mediante VBA. Formula utiliza referencias A1, nombres de función en inglés y comas. FormulaR1C1 utiliza referencias R1C1, también en inglés y con comas. Las propiedades Local emplean el idioma y el separador del usuario.

Sub TotalPorFila()
    Range("D2:D10").FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub

En D2, el resultado sería =B2*C2; en D3, =B3*C3. Con referencias R1C1, la misma fórmula se adapta a cada fila.

Para escribir la fórmula en español con punto y coma:

Sub TotalPorFilaLocal()
    Range("D2:D10").FormulaR1C1Local = "=RC[-2]*RC[-1]"
End Sub

Si asignas =SUMA(A2:B2) a .Formula, VBA puede mostrar el error de tiempo de ejecución 1004. Usa la forma inglesa =SUM(A2:B2) o .FormulaLocal. Consulta la documentación oficial de Range.FormulaR1C1.

Dudas al usar búsqueda y referencia en Excel