Kimm tiene una hoja de trabajo que tiene tres columnas. La columna A contiene el apellido de una persona, la columna B contiene el nombre y la columna C contiene el número de teléfono de la persona. Si Kimm conoce el nombre y apellido de la persona (digamos que están en las celdas F1 y F2, respectivamente), se pregunta qué tipo de fórmula de búsqueda usaría para devolver el número de teléfono de la primera persona que coincida con ese nombre y apellido.

En realidad, hay varias fórmulas diferentes que puede utilizar para averiguar el número de teléfono. La mayoría de los enfoques implican el uso de fórmulas de matriz, que siempre se ingresan en una celda usando Ctrl + Shift + Enter. Para que las fórmulas sean más fáciles de entender, es mejor trabajar con rangos con nombre. Por ejemplo, configure los siguientes nombres:

  • Seleccione todos los apellidos, en la columna A, y asígneles un nombre como LNames.

  • Seleccione todos los nombres, en la columna B, y asígneles un nombre como FNames.

  • Seleccione todos los números de teléfono, en la columna C, y asígneles un nombre como Teléfonos.

Aquí hay una variedad de fórmulas de matriz que puede usar para encontrar el número de teléfono:

=SUMPRODUCT(--(LNames=F2),--(FNames=F1),Phones)

=INDEX(Phones,MATCH(F1&F2,FNames&LNames,))

=INDEX(Phones,INDEX(MATCH(F1&F2,FNames&LNames,0),))

=OFFSET(C1,MATCH(F1&F2,FNames&LNames,),)

Además, puede construir una fórmula de matriz que se base en la función FILA, como se muestra aquí:

=INDEX(Phones,SUMPRODUCT((F1&F2=FNames&LNames)*(ROW(FNames)-1)))

=INDEX(Phones,MIN(IF((FNames=F1)*(LNames=F2),(ROW(Phones)-1))))

Si utiliza este enfoque (basándose en la función FILA), es posible que deba ajustar la parte «-1» de las fórmulas para reflejar el número de filas que aparecen antes de sus datos reales. En este caso, la fila se reduce en uno porque la primera fila de la tabla de datos consta de los encabezados de cada columna; los datos en sí comienzan en la fila 2.

Tenga en cuenta que todas las fórmulas utilizadas hasta ahora se basan en la combinación del nombre con el apellido para hacer una comparación. Esto tiene la posibilidad de encontrar «falsos positivos» en algunos casos. Por ejemplo, digamos que dos de los nombres que tiene en sus datos son Thom Astonfield y Thomas Tonfield. Dado que el caso de las letras en los nombres no importa en estas fórmulas, cuando se combinan los nombres y apellidos de estas personas, son exactamente iguales. Por lo tanto, si está buscando el número de teléfono de Thomas Tonfield y su nombre aparece en la lista después de Thom Astonfield, siempre obtendrá el número de teléfono de Thom en lugar del de Thomas.

Para solucionar este problema potencial, es posible que desee incluir algún tipo de separador entre el nombre y el apellido. Usando una de las fórmulas anteriores en la sugerencia, todo lo que necesita hacer es hacer un ajuste, como se muestra aquí:

=INDEX(Phones,SUMPRODUCT((F1&":"&F2=FNames&":"&LNames)*(ROW(FNames)-1)))

La adición de dos puntos entre el nombre y el apellido actúa como un separador, eliminando la probabilidad de falsos positivos.

También debe darse cuenta de que si las celdas F1 y F2 contienen un nombre que no aparece en los datos, la información que obtenga de la fórmula será errónea. Dependiendo de la variación de la función utilizada, es posible que obtenga una condición de error real (como # N / A o #REF)

o puede recuperar datos erróneos. En aquellos casos en los que se devuelve una condición de error, es posible que desee ajustar su fórmula para tener en cuenta la posibilidad de no encontrar una coincidencia, de esta manera:

=IF(ISERROR(INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0))), "no phone",INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0)))

Hay algunas cosas que debe tener en cuenta al utilizar este tipo de fórmulas. En primer lugar, la fiabilidad de la información que obtenga dependerá en gran medida de la calidad de la información de su lista de datos. Si sus datos tienen errores ortográficos, contienen espacios en blanco, están ordenados en algún orden extraño o hay varias entradas para la misma persona, puede afectar lo que devuelve la fórmula.

Si no está muy seguro de la calidad de sus datos, es posible que desee simplemente utilizar las capacidades de filtrado de Excel en lugar de una fórmula. Aplique un autofiltro y podrá usar las dos primeras columnas de sus datos para elegir el nombre y el apellido. Esto luego devolverá los números de teléfono de la persona que seleccione. Es muy sencillo de hacer y facilita la selección de los datos que necesita.

Por último, debe darse cuenta de que existen otros enfoques que puede utilizar para abordar el problema. Por ejemplo, puede crear una fórmula que utilice la función DGET, pero hacerlo requeriría la adición de una pequeña tabla de criterios a su hoja de trabajo o libro de trabajo. Dado que Kimm especificó que no podía agregar resultados intermedios en una hoja de trabajo, se tomó una decisión editorial para no incluir la función DGET como solución, ya que requeriría la adición de la tabla de criterios.

Además, si se siente cómodo con el uso de macros, también puede crear una función definida por el usuario que examine los datos y devuelva el número de teléfono solicitado. El beneficio de utilizar este enfoque es que le proporciona una mayor flexibilidad para procesar la información que la función realmente devuelve.

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (10479) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365. Puede encontrar una versión de este consejo para la interfaz de menú anterior de Excel aquí:

link: / excel-Pulling_a_Phone_Number_with_a_Known_First_and_Last_Name [Extraer un número de teléfono con un nombre y apellido conocidos].