Funciones de Búsqueda y Referencia

Este tipo de funciones están diseñadas para trabajar con listas o matrices de datos, permitiéndonos buscar, localizar y extraer información de la matríz de búsqueda (lista de datos), a partir de una condición, posición de fila/columna ó intesección de campos dentro de la tabla o lista de datos.

06-Jun-2016

La función BUSCARV

La función BUSCARV en Excel nos permite buscar un valor dentro de un rango de datos, es decir, nos ayuda a obtener el valor de una tabla que coincide con el valor que estamos buscando. Un ejemplo sencillo que podemos resolver con la función BUSCARV es la búsqueda dentro de un directorio telefónico.

Si queremos tener éxito para encontrar el teléfono de una persona dentro del directorio telefónico de nuestra localidad debemos tener el nombre completo de la persona. Posteriormente habrá que buscar el nombre dentro del directorio telefónico para entonces obtener el número correcto.

Crear una tabla de búsqueda

Para poder utilizar la función BUSCARV debemos cumplir con algunas condiciones en nuestros datos. En primer lugar debemos tener la información organizada de manera vertical con los valores por debajo de cada columna. Esto es necesario porque la función BUSCARV recorre los datos de manera vertical (por eso la “V” en su nombre) hasta encontrar la coincidencia del valor que buscamos.
Por ejemplo, nuestro directorio telefónico debería estar organizado de la siguiente manera:

Lista o Matriz de datos

Otra condición que forzosamente debemos cumplir es que la primera columna de nuestros datos debe ser la columna llave, es decir, los valores de esa columna deben identificar de manera única a cada una de las filas de datos. En este ejemplo la columna Nombre servirá como la columna llave porque no hay dos personas que se llamen igual.

Algo que debemos cuidar con la tabla de búsqueda es no dejar filas en blanco en medio de la tabla, ni columnas completamente vacías y  que si existen otras tablas de datos en la misma hoja de Excel debes dejar al menos una fila en blanco por debajo y una columna en blanco a la derecha de la tabla donde se realizará la búsqueda. Una vez que la tabla de búsqueda está lista podemos utilizar la función BUSCARV.

Sintaxis de la función BUSCARV

La función BUSCARV tiene 4 argumentos:

Argumentos función BUSCARV
  • Valor_buscado (obligatorio): Este es el valor que se va a buscar en la primera columna de la tabla. Podemos colocar el texto encerrado en comillas o podemos colocar la referencia a una celda que contenga el valor buscado. Excel no hará diferencia entre mayúsculas y minúsculas.
  • Matriz_buscar_en (obligatorio): La tabla de búsqueda que contiene todos los datos donde se tratará de encontrar la coincidencia del Valor_buscado.
  • Indicador_columnas (obligatorio): Una vez que la función BUSCARV encuentre una coincidencia del Valor_buscado nos devolverá como resultado la columna que indiquemos en este argumento. El Indicador_columnas es el número de columna que deseamos obtener siendo la primera columna de la tabla la columna número 1.
  • Ordenado (opcional): Este argumento debe ser un valor lógico, es decir, puede ser falso o verdadero. Con este argumento indicamos si la función BUSCARV realizará una búsqueda exacta (FALSO) o una búsqueda aproximada (VERDADERO). En caso de que se omita este argumento o que especifiquemos una búsqueda aproximada se recomienda que la primera columna de la tabla de búsqueda esté ordenada de manera ascendente para obtener los mejores resultados y buscará el valor mínimo o igual más próximo a Valor_buscado.

Ejemplo de la función BUSCARV

Para probar la función BUSCARV con nuestra tabla de búsqueda ejemplo que contiene información de números telefónicos seguimos los siguientes pasos:

  1. En la celda E1 colocaré el valor que deseo buscar.
  2. En la celda E2 Insertamos la función BUSCARV utilizando el asistente para funciones de Excel.
  3. Hago clic en la celda E1 para incluir la referencia de celda como 'Valor_buscado'.
  4. Para especificar el segundo argumento 'Matriz_buscar_en', debo seleccionar la tabla de datos sin incluir los títulos de columna. Para el ejemplo será el rango de datos A2:B11.
  5. Como tercer argumento 'Indicador_columnas', colocaré el número 2 ya que quiero que la función BUSCARV me devuelva el número de teléfono de la persona indicada en la celda E1. Recuerda que la numeración de columnas empieza con el 1 y por lo tanto la columna Teléfono es la columna número 2.
  6. Para el último argumento 'Ordenado', de la función especificaré el valor FALSO ya que deseo hacer una búsqueda exacta del nombre.
Argumentos función BUSCARV

Observa el resultado de la función recién descrita:

Ejemplo función BUSCARV

Una ventaja de haber colocado el valor buscado en la celda E1 es que podemos modificar su valor para buscar el teléfono de otra persona y la función BUSCARV actualizará el resultado automáticamente.

Ejemplo función BUSCARV

Errores comunes al usar la función BUSCARV

  • Si la columna llave no tiene valores únicos para cada fila entonces la función BUSCARV devolverá el primer resultado encontrado que concuerde con el valor buscado.
  • Si especificamos un indicador de columna mayor al número de columnas de la tabla obtendremos un error de tipo #REF!
  • Si colocamos el indicador de columna igual a cero la función BUSCARV devolverá un error de tipo #VALOR!
  • Si configuramos la función BUSCARV para realizar una búsqueda exacta, pero no encuentra el valor buscado, entonces la función devolverá un error de tipo #N/A.

La función BUSCARV es una de las funciones más importantes en Excel. Es necesario que dediques tiempo para aprender correctamente su uso y verás que podrás sacar mucho provecho de esta función.

Capturar Errores con la Función SI.ERROR

El uso de la función SI.ERROR para capturar los errores es muy sencillo, solamente debemos incluir el resultado de la función BUSCARV como el primer argumento de la función y el segundo argumento nos permite especificar el texto a mostrar en caso de obtener un error.

Imaginemos que en nuestro ejemplo anterior dejamos la celda E1 en blanco o que indicamos el nombre de un contacto que no existe. Ante esta situación la función BUSCARV devolverá un error de tipo #N/A. Para solucionarlo, podemos utilizar la función SI.ERROR para que en el caso de que se produzca algún error nos muestre un mensaje de advertencia en la celda y si no se produce ningún error que nos devuelva el teléfono del contacto.

Error en función BUSCARV

Sin utilizar el control de errores obtenemos #N/A

Edito la fórmula escrita en la celda E1, selecciono todo menos el signo ‘=’ y lo corto para poder pegarlo más adelante.

Inserto la función SI.ERROR, en su primer argumento pego lo que había cortado de la fórmula y en su segundo argumento escribo el mensaje con el que quiero advertir ‘Celda vacía o contacto no existe’.

Argumentos función SI.ERROR

El resultado tras aplicar los cambios sería como el que se muestra a continuación.

Ejemplo función SI.ERROR

Funciones de Búsqueda Personalizadas

Hasta ahora hemos comentado que la función de búsqueda más importante de Excel es CONSULTAV (y lo es), pero tiene una limitación muy importante y es que la columna clave por la que se realiza la búsqueda es obligatoriamente la primera del rango o matriz de búsqueda.

Esto nos impide realizar búsquedas por los valores contenidos en el resto de las columnas.

Para subsanar esta carencia, podemos crearnos funciones de búsqueda personalizadas que por medio de la combinación de dos funciones de Excel (COINCIDIR e INDICE) que nos permitan realizar búsquedas por el valor de cualquiera de las columnas del rango.

La función COINCIDIR

¿Qué hace?

Busca un valor dentro de una lista y devuelve la posición relativa del valor dentro del rango. La función COINCIDIR en Excel nos ayuda a localizar un elemento dentro de un rango de celdas y nos devuelve su posición. En otras palabras, la función COINCIDIR nos ayuda a obtener el número de fila que ocupa el elemento buscado.

Sintaxis

COINCIDIR(valor_buscado, rango, [tipo_de_coincidencia])

La función tiene tres argumentos:

Argumentos función COINCIDIR
  • valor_buscado (obligatorio): Valor que se buscará en el rango
  • rango (obligatorio): El rango de celdas con los valores.
  • tipo_de_coincidencia (opcional): Indica si será una coincidencia exacta ó aproximada. Si se omite se toma como aproximada.

El Tipo_de_coincidencia especificado en la función tendrá un efecto en la manera en que se hace la búsqueda. Los siguientes valores son los posibles para el tercer argumento de la función COINCIDIR:

  • 1: La función COINCIDIR encontrará el valor más grande que sea menor o igual al valor buscado. La matriz de búsqueda debe estar ordenada de manera ascendente (los valores tienen que colocarse en orden ascendente).
  • 0: La función COINCIDIR encontrará el primer valor que sea exactamente igual al valor buscado. La matriz de búsqueda puede estar en cualquier orden (da igual el orden en el que estén colocados los valores).
  • -1: La función COINCIDIR encontrará el valor más pequeño que sea mayor o igual que el valor buscado. La matriz de búsqueda debe estar ordenada de manera descendente (los valores se tienen que colocar en orden descendente).

Cuando no se especifica el tercer argumento de la función COINCIDIR se utilizará de manera predeterminada el valor 1.

Ejemplos de la función COINCIDIR

Comenzaré con un ejemplo de una búsqueda exacta con la función COINCIDIR. Para ello tengo una lista de valores en la columna A y utilizaré la siguiente fórmula:

=COINCIDIR("EFG", A1:A10, 0)

Observa el resultado de la fórmula:

Ejemplo función COINCIDIR

La función COINCIDIR nos devuelve el número 5 que es precisamente la posición que ocupa el valor buscado. Ahora observa lo que sucede al utilizar la siguiente fórmula:

=COINCIDIR("JOX", A1:A10)

En primer lugar debemos saber que el valor “JOX” no está dentro de la lista así que no habría una coincidencia exacta, pero al omitir el tercer argumento de la función estoy indicando que deseo hacer una búsqueda aproximada. Observa el resultado:

Ejemplo función COINCIDIR

La función COINCIDIR devuelve el número 9 porque es el elemento que contiene al menos una de las letras del valor buscado y es, por lo tanto, la mejor coincidencia encontrada.

Notas adicionales sobre la función COINCIDIR

Algunas notas adicionales sobre la función COINCIDIR son las siguientes:

  • La función COINCIDIR no hace diferencia entre mayúsculas y minúsculas.
  • Cuando la función COINCIDIR no encuentra alguna coincidencia devolverá el tipo de error #N/A

En el ejemplo tengo una lista de empleados que contiene el número de empleado y su nombre. Si quiero saber en qué posición se encuentra el empleado 45362 solamente debo utilizar la función COINCIDIR:

Notas función COINCIDIR

En la celda D1 he colocado el número de empleado que deseo encontrar. Y los parámetros de la función son los siguientes:

Parámetros de la función COINCIDIR

El primer parámetro de la función COINCIDIR es el valor buscado, que en el ejemplo es el valor que he colocado en la celda D1. El segundo parámetro es la matriz donde se buscará el valor y que es el rango de mi lista de valores A1:A10. El último parámetro indica el tipo de coincidencia que deseas. Para el ejemplo he puesto el valor 0 que significa que necesito una coincidencia exacta.

El resultado es un número entero, el cual indica la posición en donde la función ha encontrado el valor especificado. Debes considerar que la función regresa la posición de la primera aparición del valor, es decir, si el valor buscado está repetido dentro de la lista la función regresara la posición del primer elemento que encuentre sin importar que existan más elementos.

Para encontrar la posición de otro elemento solamente debo cambiar el valor buscado y la función COINCIDIR obtendrá su posición:

Parámetros función COINCIDIR

La función INDICE

¿Qué hace?

Devuelve el valor de la celda que se encuentra en la intersección de una fila y una columna dentro de una matriz especificada.

La función INDICE en Excel nos ayuda a obtener el valor de una celda dentro de una matriz especificando el número de fila y columna. Esta función tiene dos formas de uso: de forma matricial y de forma de referencia.

Forma matricial de la función INDICE

En su forma matricial, la función INDICE en Excel nos ayuda a obtener el valor de la celda que se encuentra justamente en el cruce de la fila y de la columna que hayamos especificado. La sintaxis de la función INDICE es la siguiente:

Forma matricial de la función INDICE

INDICE(matriz, núm_fila, [núm_columna])

  • matriz (obligatorio): Rango de celdas que compone la matriz donde se hará la intersección.
  • núm_fila (obligatorio): Fila de la matriz que contiene el valor buscado.
  • núm_columna (opcional): Columna de la matriz que contiene el valor buscado.

Tanto el Núm_fila como el Núm_columna comienzan con el número 1 que indica la primera fila o la primera columna de nuestra matriz. Ambos argumentos deben indicar una celda válida dentro de la matriz o de lo contrario recibiremos un error del tipo #¡REF!.

Ejemplo de la función INDICE en su forma matricial

Observa la siguiente tabla de datos que contiene los montos de venta de varios productos correspondientes a diferentes meses del año.

Ejemplo de la función INDICE en su forma Matricial

En la celda I3 tengo la función INDICE que me ayuda a obtener el valor de la celda que se encuentra en la fila 3 y en la columna 2 de la matriz (B2:E9) lo cual corresponde a las ventas del Producto 3 en el mes de Febrero.

Es importante que observéis que los números de fila y de columna que se pasan como argumentos  a la función INDICE no son los números de fila y de columna de la hoja de Excel sino de la matriz que se especifica como primer argumento de la función.

Forma de referencia de la función INDICE

La segunda forma de uso de la función INDICE en Excel es similar a la anterior pero con la diferencia de que en lugar de especificar una sola matriz podemos indicar más de una matriz. Bajo esta forma, la función INDICE tiene una sintaxis un poco diferente que el método anterior:

Forma de referencia de la función INDICE
  • Ref (obligatorio): La referencia a las matrices que tienen los datos.
  • Núm_fila (obligatorio): El número de fila de la celda que deseamos obtener.
  • Núm_columna (opcional): El número de columna de la celda a obtener.
  • Núm_área (opcional): Ya que Ref puede tener especificados varios rangos como A1:C6, A8:C11 entonces Núm_área indicará cual de los rangos se debe utilizar.

Si Núm_fila y Núm_columna indican una celda fuera de la matriz recibiremos un error del tipo #¡REF!.

Ejemplo de la función INDICE en su forma de referencia

He modificado un poco el ejemplo anterior para explicar este uso de la función INDICE. Observar que en este ejemplo tengo dos matrices que contienen los datos: C3:F6 y C8:F11. Puedo especificar a la función INDICE ambas matrices y con el argumento Núm_área indicar cuál de los dos rangos utilizar. La función buscará el valor ubicado en la fila 3 y columna 2 en la primera área:

Ejemplo de la función INDICE en su forma de referencia

Al utilizar la primera matriz obtenemos el valor del Producto 3 (Categoría A) y el mes de Febrero. Ahora observar lo que sucede cuando cambio solamente el valor del argumento Núm_Área con el valor 2:

Ejemplo de la función INDICE en su forma de referencia

Ahora la función INDICE utiliza la segunda matriz (C8:F11) y obtiene el valor del Producto 3 (Categoría B) para el mes de Febrero y todo con solo modificar el argumento Núm_Área.

Anidando funciones INDICE y COINCIDIR

La unión mejora la búsqueda

Hasta ahora hemos visto las funciones INDICE y COINCIDIR por separado, donde los argumentos referentes a filas y columnas se los indicábamos por medio de valores numéricos, pero si aprovechamos la función COINCIDIR para que suministre este número de fila o columna a la función INDICE, podemos conseguir una función de búsqueda personalizada.

Veamos un ejemplo

Supongamos que tenemos una lista de productos, donde tenemos las unidades vendidas de cada uno y deseamos obtener el nombre del producto que ha tenido más ventas y el que menos.

Anidar funciones INDICE y COINCIDIR

Lo primero que haremos es obtener el valor máximo y mínimo de entre las ventas, que nos servirán para realizar la búsqueda del nombre del artículo.

Con la función COINCIDIR buscaremos el valor dentro de la columna de las ventas, para obtener el nº de fila donde se encuentra y lo utilizaremos como valor del argumento fila en la función INDICE para obtener el valor de la celda de la columna de artículos que ocupa esa posición en la columna 1.

Anidar funciones INDICE y COINCIDIR

Observar como el argumento que hace referencia a num_fila en la función INDICE se obtiene por medio de la función COINCIDIR que busca el valor del máximo de unidades en la columna de ventas. Como el rango utilizado por la función INDICE está formado por una única columna su argumento num_columna es 1.

Hagamos lo mismo con el ejemplo visto en el anterior apartado de la función INDICE.  Si en lugar de indicar como valor 3 para la fila y 2 para la columna con los que obteníamos las ventas del Producto 3 en el mes de Febrero, utilizamos sus nombres para realizar la búsqueda, podremos cambiarlos y obtener cualquier valor del rango sin tener que modificar la fórmula.

Veamos como:

Anidar funciones INDICE y COINCIDIR
Anidar funciones INDICE y COINCIDIR

Observar como las funciones COINCIDIR utilizan los valores escritos en las celdas G8 y H8 para obtener el número de la fila y de la columna que le hacen falta a la función INDICE para localizar el datos en el rango de las ventas.

 

1 Comentario

001
avatar
Luis Roca
06.02.16 06:28
Lo más seguro sea que no tienes instalada la actualización SP2 de la Office. Suele ocurrir en su versión 2010